英文:
Mysql Select statement with GROUP BY
问题
这是我的选择SQL查询及其结果：
SELECT line, machine_type, COUNT(*) as linewise FROM dr_scan GROUP BY machine_type, line ORDER BY dr_scan.line ASC
| 行 | 机器类型 | 按行计数 |
|---|---|---|
| 1 | 按钮附加 | 4 |
| 1 | 双针 | 1 |
| 1 | 平缝 | 1 |
| 1 | 单针 | 1 |
| 5 | 按钮附加 | 2 |
| 5 | 平缝 | 1 |
| 5 | 双针 | 1 |
我想将这个表格制作成如下所示：
| 行 | 机器类型 | 按行计数 |
|---|---|---|
| 1 | 按钮附加 | 4 |
| 双针 | 1 | |
| 平缝 | 1 | |
| 单针 | 1 | |
| 5 | 按钮附加 | 2 |
| 平缝 | 1 | |
| 双针 | 1 |
英文:
Hi this is my select sql query and its result :
SELECT line,machine_type, COUNT(*) as linewise FROM dr_scan GROUP BY machine_type,line ORDER BY dr_scan.line ASC
| line | machine_type | linewise |
|---|---|---|
| 1 | Button Attach | 4 |
| 1 | Double Needle | 1 |
| 1 | Flatlock | 1 |
| 1 | Single Needle | 1 |
| 5 | Button Attach | 2 |
| 5 | Flatlock | 1 |
| 5 | Double Needle | 1 |
I want to make this table as below
| line | machine_type | linewise |
|---|---|---|
| 1 | Button Attach | 4 |
| Double Needle | 1 | |
| Flatlock | 1 | |
| Single Needle | 1 | |
| 5 | Button Attach | 2 |
| Flatlock | 1 | |
| Double Needle | 1 |
答案1
得分: 1
使用LAG函数获取前一行，并按行排序，将其放入CASE语句中，以便如果LAG的值等于行，则显示空白。
insert into @tmp(
line , machine_type , linewise
)
values(1,'Button Attach', 4)
,(1,'Double Needle', 1)
,(1,'Flatlock', 1)
,(1,'Single Needle', 1)
,(5,'Button Attach', 2)
,(5,'Flatlock', 1)
,(5,'Double Needle', 1)
select
case when lag(line) over(order by line)=line then '' else cast(line as varchar(10)) end line
,machine_type,linewise
from @tmp
输出：
line machine_type linewise
1 Button Attach 4
Double Needle 1
Flatlock 1
Single Needle 1
5 Button Attach 2
Flatlock 1
Double Needle 1
英文:
use lag to get the previous line sort by line and put it in a case statement such that if the value of lag is equal to line than display blank.
insert into @tmp(
line , machine_type , linewise
)
values(1,'Button Attach', 4)
,(1,'Double Needle', 1)
,(1,'Flatlock', 1)
,(1,'Single Needle', 1)
,(5,'Button Attach', 2)
,(5,'Flatlock', 1)
,(5,'Double Needle', 1)
select
case when lag(line) over(order by line)=line then '' else cast(line as varchar(10)) end line
,machine_type,linewise
from @tmp
output:
line machine_type linewise
1 Button Attach 4
Double Needle 1
Flatlock 1
Single Needle 1
5 Button Attach 2
Flatlock 1
Double Needle 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论