SQL排序列1，并在第2列中添加所有值。

Question

我有一个查询，得到一组显示货架位置和是否使用货架的结果。货架布局类似于："A1XXX01，A1XXX02“，然后移动到A2等等。下面的屏幕截图显示了我正在返回的结果集，我希望以如下方式对数据进行排序：

location   amountUsed   amountFree
   A1          200          100
   A2          100          500
   B1          520          100

这是我到目前为止所得到的查询，以及它得到的一小块数据的屏幕截图。

SELECT 
wl.location,
CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 0 ELSE 1 END as isUsed
FROM warehouse_locations as wl LEFT JOIN
     product_stock_warehouse as psw
     ON psw.location = wl.location 
WHERE wl.unitId_unitId LIKE '1'
GROUP BY wl.location 
order by wl.location

​

​

"isUsed“列表示货架正在使用，0表示货架是免费的。

Answer 1

有人写了一个答案，但很快就把它删除了，它成功了，下面是经过几次修改后的工作代码。

SELECT  substring(location, 1, 2) as location,
    count(case when isUsed = 1 then location END) as amountUsed,
    count(case when isUsed = 0 then location END) as amountFree,
    count(isUsed) as totalShelves
FROM    (
            SELECT  wl.location,
                    CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 0 ELSE 1 END as isUsed
            FROM    warehouse_locations as wl
            LEFT JOIN
                    product_stock_warehouse as psw
            ON      psw.location = wl.location 
            WHERE   wl.unitId_unitId LIKE '1'
            GROUP BY wl.location 
        ) t1
GROUP BY substring(location, 1, 2)
ORDER BY substring(location, 1, 2)

Answer 2

SELECT 
LEFT(wl.location,2) as shelf,
SUM(CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 0 ELSE 1 END) as isUsed,
SUM(CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 1 ELSE 0 END) as isNotUsed

FROM warehouse_locations as wl LEFT JOIN
     product_stock_warehouse as psw
     ON psw.location = wl.location 
WHERE wl.unitId_unitId LIKE '1'
GROUP BY LEFT(wl.location,2)
order by LEFT(wl.location,2)

假设货架总是2字格式的。

你可能忘了这部分的通配符吧？

wl.unitId_unitId LIKE '1'，如果你没有，它真的是这样的。您可以将其更改为wl.unitId_unitId = '1'

Answer 3

所以，看看你的问题，你似乎想把你的结果按“位置”的前两个字符分组。然后，只需稍微调整一下case语句即可。这是未经测试的，也许有一个更好的方法来做，但我认为这应该或多或少地起作用。

SELECT 
LEFT(wl.location, 2) as Location, --Gets first 2 characters
SUM(CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 0 ELSE 1 END) as TotalUsed, --Get a sum of total used
SUM(CASE WHEN COALESCE(SUM(quantity), 0) = 0 THEN 1 ELSE 0 END) as TotalUnused, --Get a sum of total unused
FROM warehouse_locations as wl LEFT JOIN
 product_stock_warehouse as psw
 ON psw.location = wl.location 
WHERE wl.unitId_unitId LIKE '1'
GROUP BY SUBSTR(wl.location, 1, 2)
ORDER by Location