是否可以输出按组排序并按组限制的表？

发布于 2025-01-12 17:28:10 字数 7652 浏览 3 评论 0原文

我有一个包含汽车表的数据库，该表有许多不同的列。我需要输出该表中按每辆车的品牌排序的内容，每个品牌只需要输出三辆汽车以及每行汽车的总数。我还需要将输出按降序排列，并附有一个名为 Ranking 的列，该列从 1 开始计数到输出的数量。

下面是我的数据库表中的示例

|Timestamp     |Email                    |Name      |Year|Make    |Model    |Car_ID|Judge_ID|Judge_Name|Racer_Turbo|Racer_Supercharged|Racer_Performance|Racer_Horsepower|Car_Overall|Engine_Modifications|Engine_Performance|Engine_Chrome|Engine_Detailing|Engine_Cleanliness|Body_Frame_Undercarriage|Body_Frame_Suspension|Body_Frame_Chrome|Body_Frame_Detailing|Body_Frame_Cleanliness|Mods_Paint|Mods_Body|Mods_Wrap|Mods_Rims|Mods_Interior|Mods_Other|Mods_ICE|Mods_Aftermarket|Mods_WIP|Mods_Overall|
|--------------|-------------------------|----------|----|--------|---------|------|--------|----------|-----------|------------------|-----------------|----------------|-----------|--------------------|------------------|-------------|----------------|------------------|------------------------|---------------------|-----------------|--------------------|----------------------|----------|---------|---------|---------|-------------|----------|--------|----------------|--------|------------|
|8/5/2018 14:10|[email protected]  |Hernando  |2015|Acura   |TLX      |48    |J04     |Bob       |0          |0                 |2                |2               |4          |4                   |0                 |2            |4               |4                 |2                       |4                    |2                |2                   |2                     |2         |2        |0        |4        |4            |4         |6       |2               |0       |4           |
|8/5/2018 15:11|[email protected]   |Noel      |2015|Jeep    |Wrangler |124   |J02     |Carl      |0          |6                 |4                |2               |4          |6                   |6                 |4            |4               |4                 |6                       |6                    |6                |6                   |6                     |4         |6        |6        |6        |6            |6         |4       |6               |4       |6           |
|8/5/2018 17:10|[email protected]   |Edan      |2015|Lexus   |Is250    |222   |J05     |Adrian    |0          |0                 |0                |0               |0          |0                   |0                 |0            |6               |6                 |6                       |0                    |0                |6                   |6                     |6         |0        |0        |0        |0            |0         |0       |0               |0       |4           |
|8/5/2018 17:34|[email protected]      |Hieronymus|1993|Honda   |Civic eG |207   |J06     |Aaron     |0          |0                 |2                |2               |2          |2                   |2                 |2            |0               |4                 |2                       |2                    |2                |2                   |2                     |2         |4        |2        |2        |0            |0         |0       |2               |2       |0           |
|8/5/2018 14:30|[email protected]  |Nickolas  |2016|Ford    |Mystang  |167   |J02     |Carl      |0          |0                 |2                |2               |0          |2                   |2                 |0            |0               |0                 |0                       |2                    |0                |2                   |2                     |2         |0        |0        |2        |0            |0         |0       |0               |0       |2           |
|8/5/2018 16:12|[email protected]    |Martin    |2013|Hyundai |Gen coupe|159   |J04     |Bob       |0          |0                 |2                |0               |0          |0                   |2                 |0            |0               |0                 |0                       |2                    |0                |2                   |2                     |0         |2        |0        |2        |0            |0         |0       |0               |0       |0           |
|8/5/2018 17:00|[email protected]|Aldridge  |2009|Infiniti|G37      |20    |J06     |Aaron     |2          |0                 |2                |2               |0          |0                   |2                 |0            |0               |2                 |2                       |2                    |2                |2                   |2                     |2         |2        |2        |4        |2            |2         |0       |2               |0       |2           |
|8/5/2018 16:11|[email protected]     |Ambros    |2009|Honda   |Oddesy   |178   |J06     |Aaron     |2          |0                 |2                |2               |2          |2                   |2                 |0            |4               |4                 |2                       |2                    |2                |4                   |4                     |4         |2        |2        |         |6            |4         |4       |6               |4       |6           |
|8/5/2018 17:29|[email protected]|Quincy    |2012|Hyundai |Celoster |30    |J04     |Bob       |0          |0                 |2                |2               |2          |2                   |2                 |4            |6               |6                 |4                       |2                    |4                |4                   |6                     |6         |4        |0        |2        |0            |0         |0       |2               |2       |4           |

预期的输出如下所示

|Ranking |Car_ID|Year   |Make  |Model      |Total|
|--------|------|-------|------|-----------|-----|
|1       |48    |2015   |Acura |TLX        |89   |
|2       |66    |2012   |Acura |MDX        |75   |
|3       |101   |2022   |Acura |TLX        |70   |
|4       |22    |2011   |Chevy |Camaro     |112  |
|5       |40    |2015   |Chevy |Corvette   |99   |
|6       |205   |2022   |Chevy |Corvette   |66   |
|7       |111   |2006   |Ford  |Mustang    |94   |
|8       |97    |2003   |Ford  |GT         |88   |
|9       |71    |2008   |Ford  |Fiesta ST  |80   |

这是我能够组合在一起的命令，它执行与我需要的类似的操作，但我不知道如何执行排名列并按总数降序排列。

SELECT Car_ID, Year, Make, Model, Racer_Turbo + Racer_Supercharged + ... + Mods_Overall FROM Carstable order by Make limit 3;

这个查询命令只返回三个结果，而不是全部，我也无法弄清楚在命令中放置 DESC 关键字的位置，以便根据总计列按降序排列它们，也不知道如何进行排名列。有什么想法吗？

原文

I have a database with a table of cars, the table has a number of different columns. I need to output the content within that table ordered by the Make of each car, only three cars from each make need to be outputted along side the total from eachh row of car. I also need to have the output ordered in descending order accompanied by a column called Ranking that counts up from 1 to however many outputs there will be.

Below is a sample from my databse table

|Timestamp     |Email                    |Name      |Year|Make    |Model    |Car_ID|Judge_ID|Judge_Name|Racer_Turbo|Racer_Supercharged|Racer_Performance|Racer_Horsepower|Car_Overall|Engine_Modifications|Engine_Performance|Engine_Chrome|Engine_Detailing|Engine_Cleanliness|Body_Frame_Undercarriage|Body_Frame_Suspension|Body_Frame_Chrome|Body_Frame_Detailing|Body_Frame_Cleanliness|Mods_Paint|Mods_Body|Mods_Wrap|Mods_Rims|Mods_Interior|Mods_Other|Mods_ICE|Mods_Aftermarket|Mods_WIP|Mods_Overall|
|--------------|-------------------------|----------|----|--------|---------|------|--------|----------|-----------|------------------|-----------------|----------------|-----------|--------------------|------------------|-------------|----------------|------------------|------------------------|---------------------|-----------------|--------------------|----------------------|----------|---------|---------|---------|-------------|----------|--------|----------------|--------|------------|
|8/5/2018 14:10|[email protected]  |Hernando  |2015|Acura   |TLX      |48    |J04     |Bob       |0          |0                 |2                |2               |4          |4                   |0                 |2            |4               |4                 |2                       |4                    |2                |2                   |2                     |2         |2        |0        |4        |4            |4         |6       |2               |0       |4           |
|8/5/2018 15:11|[email protected]   |Noel      |2015|Jeep    |Wrangler |124   |J02     |Carl      |0          |6                 |4                |2               |4          |6                   |6                 |4            |4               |4                 |6                       |6                    |6                |6                   |6                     |4         |6        |6        |6        |6            |6         |4       |6               |4       |6           |
|8/5/2018 17:10|[email protected]   |Edan      |2015|Lexus   |Is250    |222   |J05     |Adrian    |0          |0                 |0                |0               |0          |0                   |0                 |0            |6               |6                 |6                       |0                    |0                |6                   |6                     |6         |0        |0        |0        |0            |0         |0       |0               |0       |4           |
|8/5/2018 17:34|[email protected]      |Hieronymus|1993|Honda   |Civic eG |207   |J06     |Aaron     |0          |0                 |2                |2               |2          |2                   |2                 |2            |0               |4                 |2                       |2                    |2                |2                   |2                     |2         |4        |2        |2        |0            |0         |0       |2               |2       |0           |
|8/5/2018 14:30|[email protected]  |Nickolas  |2016|Ford    |Mystang  |167   |J02     |Carl      |0          |0                 |2                |2               |0          |2                   |2                 |0            |0               |0                 |0                       |2                    |0                |2                   |2                     |2         |0        |0        |2        |0            |0         |0       |0               |0       |2           |
|8/5/2018 16:12|[email protected]    |Martin    |2013|Hyundai |Gen coupe|159   |J04     |Bob       |0          |0                 |2                |0               |0          |0                   |2                 |0            |0               |0                 |0                       |2                    |0                |2                   |2                     |0         |2        |0        |2        |0            |0         |0       |0               |0       |0           |
|8/5/2018 17:00|[email protected]|Aldridge  |2009|Infiniti|G37      |20    |J06     |Aaron     |2          |0                 |2                |2               |0          |0                   |2                 |0            |0               |2                 |2                       |2                    |2                |2                   |2                     |2         |2        |2        |4        |2            |2         |0       |2               |0       |2           |
|8/5/2018 16:11|[email protected]     |Ambros    |2009|Honda   |Oddesy   |178   |J06     |Aaron     |2          |0                 |2                |2               |2          |2                   |2                 |0            |4               |4                 |2                       |2                    |2                |4                   |4                     |4         |2        |2        |         |6            |4         |4       |6               |4       |6           |
|8/5/2018 17:29|[email protected]|Quincy    |2012|Hyundai |Celoster |30    |J04     |Bob       |0          |0                 |2                |2               |2          |2                   |2                 |4            |6               |6                 |4                       |2                    |4                |4                   |6                     |6         |4        |0        |2        |0            |0         |0       |2               |2       |4           |

The expected output is something like this below

|Ranking |Car_ID|Year   |Make  |Model      |Total|
|--------|------|-------|------|-----------|-----|
|1       |48    |2015   |Acura |TLX        |89   |
|2       |66    |2012   |Acura |MDX        |75   |
|3       |101   |2022   |Acura |TLX        |70   |
|4       |22    |2011   |Chevy |Camaro     |112  |
|5       |40    |2015   |Chevy |Corvette   |99   |
|6       |205   |2022   |Chevy |Corvette   |66   |
|7       |111   |2006   |Ford  |Mustang    |94   |
|8       |97    |2003   |Ford  |GT         |88   |
|9       |71    |2008   |Ford  |Fiesta ST  |80   |

Here's the command I've been been able to put together which does something similar to what I need, but I can't figure out how to do the ranking column and order by descending from the total.

SELECT Car_ID, Year, Make, Model, Racer_Turbo + Racer_Supercharged + ... + Mods_Overall FROM Carstable order by Make limit 3;

This query command only returned three results instead of all, I also can't figure out where to put the DESC keyword in the command in order to have them listed in descending order based on the total column or how to do the ranking column as well. Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随遇而安 2025-01-19 17:28:10

使用 CTE 返回每行的 Total 列，并使用 ROW_NUMBER() 窗口函数为每个 Make 选取前 3 行并创建Ranking 列：

WITH cte AS (
  SELECT *, 
       Racer_Turbo + Racer_Supercharged + Racer_Performance + Racer_Horsepower + 
       Car_Overall + 
       Engine_Modifications + Engine_Performance + Engine_Chrome + Engine_Detailing + Engine_Cleanliness + 
       Body_Frame_Undercarriage + Body_Frame_Suspension + Body_Frame_Chrome + Body_Frame_Detailing + Body_Frame_Cleanliness + 
       Mods_Paint + Mods_Body + Mods_Wrap + Mods_Rims + Mods_Interior + Mods_Other + Mods_ICE + Mods_Aftermarket + Mods_WIP + Mods_Overall Total
  FROM carstable
)
SELECT ROW_NUMBER() OVER (ORDER BY Make, Total DESC) Ranking,
       Car_ID, Year, Make, Model, Total
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Make ORDER BY Total) rn FROM cte) 
WHERE rn <= 3
ORDER BY Make, Total DESC;

请参阅演示。

Use a CTE which returns the column Total for each row and ROW_NUMBER() window function to pick the first 3 rows for each Make and to create the column Ranking:

WITH cte AS (
  SELECT *, 
       Racer_Turbo + Racer_Supercharged + Racer_Performance + Racer_Horsepower + 
       Car_Overall + 
       Engine_Modifications + Engine_Performance + Engine_Chrome + Engine_Detailing + Engine_Cleanliness + 
       Body_Frame_Undercarriage + Body_Frame_Suspension + Body_Frame_Chrome + Body_Frame_Detailing + Body_Frame_Cleanliness + 
       Mods_Paint + Mods_Body + Mods_Wrap + Mods_Rims + Mods_Interior + Mods_Other + Mods_ICE + Mods_Aftermarket + Mods_WIP + Mods_Overall Total
  FROM carstable
)
SELECT ROW_NUMBER() OVER (ORDER BY Make, Total DESC) Ranking,
       Car_ID, Year, Make, Model, Total
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Make ORDER BY Total) rn FROM cte) 
WHERE rn <= 3
ORDER BY Make, Total DESC;

See the demo.

回复收藏 0 原文

~没有更多了~