英文:
Choose between two columns based on aggregate of another column
问题
如果PEOPLE与其他行相同但COMPANY不同，则给我SERVICE_1；如果PEOPLE与其他行相同且COMPANY也相同，则给我SERVICE2。我该如何在T-SQL中编写这段代码？可以使用row_number子句来实现吗？
原始数据：
| PEOPLE | COMPANY | SERVICE_1 | SERVICE_2 |
|---|---|---|---|
| KRISH | AA | HYDRO | WATER |
| KRISH | BB | NULL | WATER |
| JOHN | CC | NULL | ROAD |
| JOHN | CC | NULL | ELECY |
| JOHN | CC | NULL | GAS |
期望结果：
| PEOPLE | COMPANY | SERVICE |
|---|---|---|
| KRISH | AA | HYDRO |
| KRISH | BB | NULL |
| JOHN | CC | ROAD |
| JOHN | CC | ELECY |
| JOHN | CC | GAS |
英文:
If PEOPLE are the same as in other rows but COMPANY is different, then give me SERVICE_1; if PEOPLE are the same like other rows and COMPANY are also the same then give me SERVICE2. How can I write code for this in T-SQL? Can it be achieved using the row_number clause?
Original data:
| PEOPLE | COMPANY | SERVICE_1 | SERVICE_2 |
|---|---|---|---|
| KRISH | AA | HYDRO | WATER |
| KRISH | BB | NULL | WATER |
| JOHN | CC | NULL | ROAD |
| JOHN | CC | NULL | ELECY |
| JOHN | CC | NULL | GAS |
Desired results:
| PEOPLE | COMPANY | SERVICE |
|---|---|---|
| KRISH | AA | HYDRO |
| KRISH | BB | NULL |
| JOHN | CC | ROAD |
| JOHN | CC | ELECY |
| JOHN | CC | GAS |
答案1
得分: 2
以下是翻译好的部分：
也许有数种方法可以实现这个目标。其中一种方法是首先分别计算不同的公司数量，然后使用 CASE 表达式将其连接在一起，通过此方式公开服务（示例）：
WITH cte AS
(
SELECT PEOPLE, c = COUNT(DISTINCT COMPANY)
FROM dbo.Something GROUP BY PEOPLE
)
SELECT cte.PEOPLE, s.COMPANY,
SERVICE = CASE WHEN cte.c = 1 THEN s.SERVICE_2 ELSE s.SERVICE_1 END
FROM cte JOIN dbo.Something AS s
ON cte.PEOPLE = s.PEOPLE;
英文:
Probably a dozen ways to do this. One is you can count the distinct companies separately and then join, exposing the service with a CASE expression (example):
WITH cte AS
(
SELECT PEOPLE, c = COUNT(DISTINCT COMPANY)
FROM dbo.Something GROUP BY PEOPLE
)
SELECT cte.PEOPLE, s.COMPANY,
SERVICE = CASE WHEN cte.c = 1 THEN s.SERVICE_2 ELSE s.SERVICE_1 END
FROM cte JOIN dbo.Something AS s
ON cte.PEOPLE = s.PEOPLE;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论