在SQL中生成二维结果表

Question

我有一个每天刷新的项目列表，其中每个项目都属于四个价格区间中的一个。

以下查询将按日期拉取每个价格区间中的商品计数，但它将每个区间/日期组合放入其自己的行中。

SELECT PB.band, Count(L.ID) as Count
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
Group by date, PB.band
order by date asc, band asc

结果集如下所示(用于说明的两天时间)：

date    band    Count
2010-12-08  1   20
2010-12-08  2   17
2010-12-08  3   32
2010-12-08  4   26
2010-12-09  1   19
2010-12-09  2   16
2010-12-09  3   33
2010-12-09  4   27

虽然我可以通过迭代来创建我真正想要的表，但是有没有一种合理的方法来编写查询来以2-D形式返回数据，如下所示：

date        band1   band2   band2   band3   
2010-12-08  20      17      32      26
2010-12-09  19      16      33      27

谢谢大家。

Answer 1

SELECT [date],
       COUNT(CASE WHEN PB.band = 1 THEN L.ID END) AS band1,
       COUNT(CASE WHEN PB.band = 2 THEN L.ID END) AS band2,
       COUNT(CASE WHEN PB.band = 3 THEN L.ID END) AS band3,
       COUNT(CASE WHEN PB.band = 4 THEN L.ID END) AS band4
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
Group by date
order by date asc

Answer 2

查看T-SQL中的PIVOT子句。=>MSDN Page

我不确定查询的确切形式，但它应该是这样的(如果可以工作，请告诉我)：

SELECT [date],[1],[2],[3],[4]
FROM ListItem L
join PriceBand PB on L.price >= PB.loprice and L.price <= PB.hiprice
Where ListID = 1  and ranking <= 100 and band >= 1 and band <= 4
PIVOT(COUNT(L.ID) FOR PB.band IN([1],[2],[3],[4])) as pvt   
order by [date] asc

Answer 3

首先，“”是列的可怕的日期名称，所以我希望这只是一个示例。其次，由于您没有对它使用别名，所以我不知道它实际在哪个表中，所以我最好的猜测是：

SELECT
    LI.list_date,
    SUM(CASE WHEN PB.band = 1 THEN 1 ELSE 0 END) AS band_1_count,
    SUM(CASE WHEN PB.band = 2 THEN 1 ELSE 0 END) AS band_2_count,
    SUM(CASE WHEN PB.band = 3 THEN 1 ELSE 0 END) AS band_3_count,
    SUM(CASE WHEN PB.band = 4 THEN 1 ELSE 0 END) AS band_4_count
FROM
    List_Item LI
INNER JOIN Price_Band PB ON
    PB.low_price <= LI.price AND
    PR.high_price >= LI.price AND
    PB.band <= 4
WHERE
    LI.list_id = 1 AND
    LI.ranking <= 100 AND
GROUP BY
    LI.list_date
ORDER BY
    LI.list_date