mysql - 两个字段的组合分组

Question

我只能访问数据库的 SELECT 和 WHERE 子句。除了填写 SELECT 和 FROM 之间以及 WHERE 之后的空白(“SELECT _ _ _ _ _ _ FROM TABLE WHERE _ _ _ _”)

在数据库中，有四个字段(实际上更多，但这些是相关的):“TShirt1”、“TShirt1Size”、“TShirt2”和“TShirt2Size”。

TShirt1 和 TShirt2 都包含“RedCrew”、“BlueSlim”或“GreyV”之一

TShirt1Size 和 TShirt2Size 都包含“S”、“M”、“L”或“XL”之一

我想知道我是否可以创建一个查询来返回每个组合的总数。 如果我没有两个字段，那就是

SELECT TShirt, TShirtSize, count(*) FROM BookingsTable WHERE 1 GROUP BY TShirt, TShirtSize

所以我会得到这样的结果

RedCrew, L, 2
BlueSlim, M, 4

等

有什么方法可以获得相同或相似的输出，但结合了 TShirt1 和 TShirt2 的结果？

编辑:对造成的困惑表示歉意。实际的表格应该是这样的(输入未测试):

create table tshirt (
    TShirt_id int, 
    TShirt1 varchar(10), 
    TShirt1Size varchar(2), 
    TShirt2 varchar(10),
    TShirt2Size varchar(2)
);

insert into tshirt values (1, 'RedCrew', 'XL', 'BlueSlim', 'M'); 
insert into tshirt values (2, 'BlueSlim', 'L', 'RedCrew', 'L'); 
insert into tshirt values (3, 'GreyV', 'L', 'BlueSlim', 'M');
insert into tshirt values (4, 'BlueSlim', 'M', '', ''); 
insert into tshirt values (5, '', '', 'GreyV', 'L'); 
insert into tshirt values (6, 'BlueSlim', 'S', 'BlueSlim', 'L'); 

我想要的结果集(如果我的数学是正确的)将是:

BlueSlim, S, 1
BlueSlim, M, 4
BlueSlim, L, 2
GreyV, L, 2
RedCrew, L, 1
RedCrew, XL, 1

Answer 1

感谢您的各种建议，但它们都超出了我的有限范围。我最终得到以下查询:

SELECT sum( if ((TShirt1='RedCrew' AND TShirt1Size='S') OR (TShirt2='RedCrew' AND TShirt2Size='S'), if ( (TShirt1='RedCrew' AND TShirt1Size='S') AND (TShirt2='RedCrew' AND TShirt2Size='S'), 2, 1), 0)) AS 'RedCrew S',
sum( if ((TShirt1='RedCrew' AND TShirt1Size='M') OR (TShirt2='RedCrew' AND TShirt2Size='M'), if ( (TShirt1='RedCrew' AND TShirt1Size='M') AND (TShirt2='RedCrew' AND TShirt2Size='M'), 2, 1), 0)) AS 'RedCrew M',
sum( if ((TShirt1='RedCrew' AND TShirt1Size='L') OR (TShirt2='RedCrew' AND TShirt2Size='L'), if ( (TShirt1='RedCrew' AND TShirt1Size='L') AND (TShirt2='RedCrew' AND TShirt2Size='L'), 2, 1), 0)) AS 'RedCrew L',
sum( if ((TShirt1='RedCrew' AND TShirt1Size='XL') OR (TShirt2='RedCrew' AND TShirt2Size='XL'), if ( (TShirt1='RedCrew' AND TShirt1Size='XL') AND (TShirt2='RedCrew' AND TShirt2Size='XL'), 2, 1), 0)) AS 'RedCrew XL',
sum( if ((TShirt1='BlueSlim' AND TShirt1Size='S') OR (TShirt2='BlueSlim' AND TShirt2Size='S'), if ( (TShirt1='BlueSlim' AND TShirt1Size='S') AND (TShirt2='BlueSlim' AND TShirt2Size='S'), 2, 1), 0)) AS 'BlueSlim S',
sum( if ((TShirt1='BlueSlim' AND TShirt1Size='M') OR (TShirt2='BlueSlim' AND TShirt2Size='M'), if ( (TShirt1='BlueSlim' AND TShirt1Size='M') AND (TShirt2='BlueSlim' AND TShirt2Size='M'), 2, 1), 0)) AS 'BlueSlim M',
sum( if ((TShirt1='BlueSlim' AND TShirt1Size='L') OR (TShirt2='BlueSlim' AND TShirt2Size='L'), if ( (TShirt1='BlueSlim' AND TShirt1Size='L') AND (TShirt2='BlueSlim' AND TShirt2Size='L'), 2, 1), 0)) AS 'BlueSlim L',
sum( if ((TShirt1='BlueSlim' AND TShirt1Size='XL') OR (TShirt2='BlueSlim' AND TShirt2Size='XL'), if ( (TShirt1='BlueSlim' AND TShirt1Size='XL') AND (TShirt2='BlueSlim' AND TShirt2Size='XL'), 2, 1), 0)) AS 'BlueSlim XL',
sum( if ((TShirt1='GreyV' AND TShirt1Size='S') OR (TShirt2='GreyV' AND TShirt2Size='S'), if ( (TShirt1='GreyV' AND TShirt1Size='S') AND (TShirt2='GreyV' AND TShirt2Size='S'), 2, 1), 0)) AS 'GreyV S',
sum( if ((TShirt1='GreyV' AND TShirt1Size='M') OR (TShirt2='GreyV' AND TShirt2Size='M'), if ( (TShirt1='GreyV' AND TShirt1Size='M') AND (TShirt2='GreyV' AND TShirt2Size='M'), 2, 1), 0)) AS 'GreyV M',
sum( if ((TShirt1='GreyV' AND TShirt1Size='L') OR (TShirt2='GreyV' AND TShirt2Size='L'), if ( (TShirt1='GreyV' AND TShirt1Size='L') AND (TShirt2='GreyV' AND TShirt2Size='L'), 2, 1), 0)) AS 'GreyV L',
sum( if ((TShirt1='GreyV' AND TShirt1Size='XL') OR (TShirt2='GreyV' AND TShirt2Size='XL'), if ( (TShirt1='GreyV' AND TShirt1Size='XL') AND (TShirt2='GreyV' AND TShirt2Size='XL'), 2, 1), 0)) AS 'GreyV XL' 
WHERE 1;

我用 PHP 生成的:

$tees=array('RedCrew','BlueSlim','GreyV');
$sizes=array('S','M','L','XL');
foreach ($tees as $t)
{
    foreach ($sizes as $s)
    {
        print "sum( if ((TShirt1='$t' AND TShirt1Size='$s') OR (TShirt2='$t' AND TShirt2Size='$s'), if ( (TShirt1='$t'  AND TShirt1Size='$s') AND (TShirt2='$t' AND TShirt2Size='$s'), 2, 1), 0)) AS '$t $s',<br>";
    }
}

它没有给出完全正确的答案格式，但它告诉我我需要知道的内容!