SQL对列1进行排序并在第2列中添加所有值

I have got a query that gets back a set of results that shows the shelf location and if the shelf is being used. The shelf layouts are like: "A1XXX01, A1XXX02" and then moving onto A2 and so on. The screenshot bellow shows the result set I am currently getting back, I want to sort the data in a way that it shows like this:

我有一个查询返回一组结果，显示货架位置和货架是否正在使用。架子布局如下：“A1XXX01，A1XXX02”，然后移动到A2，依此类推。下面的屏幕截图显示了我目前正在返回的结果集，我想以这样的方式对数据进行排序：

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

This is the query I have so far and a screenshot of a small piece of data it gets back

这是我到目前为止的查询以及它返回的一小段数据的屏幕截图

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

The "isUsed" column is 1 to show that the shelf is in use and 0 to show that it is free

“isUsed”列为1表示货架正在使用，0表示它是免费的

3 个解决方案

#1

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)

Assuming that shelves are always on a 2-character format.

假设货架总是采用双字符格式。

P.S. You may have forgotten a wildcard on this part?

附：您可能忘记了这部分的通配符？

wl.unitId_unitId LIKE '1' If you haven't, and it's really just like that. you can just change that to wl.unitId_unitId = '1'

wl.unitId_unitId LIKE'1'如果你没有，那就真的那样。你可以改成wl.unitId_unitId ='1'

#2

So, looking at your question it appears you want to group your results by the first 2 characters of 'location'. Then you just need to tweak the case statements slightly. This is untested and there's probably a better way of doing it, but I think this should more or less work.

因此，看看您的问题，您似乎希望按“位置”的前2个字符对结果进行分组。然后你只需稍微调整一下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

#3

Someone put an answer but shortly deleted it after and it worked, here is the working code after a few tweaks

有人提出了答案，但很快就将其删除并且有效，这是经过一些调整后的工作代码

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)

#1

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)