主页 / dba / 问题 / 69618
Accepted
dotancohen
Asked: 2014-07-04 01:54:57 +0800 CST

带有引用子查询的 SELECT 查询

  • 772

如何在子查询中引用当前分组的字段？

考虑一个包含以下数据的表：

Client    Status    Size    
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

我试图在单个查询中获取以下数据：

Client     A      B
   1      500   1500
   2     2000    800

我一直在玩这方面的变体，但绝对没有成功：

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

每个客户端的状态不重复，也就是说，每个客户端应该只有一行具有给定的状态。出于这个问题的目的，我们可以假设每种状态只有一个条目。

请注意，大约有十万行：大约 2 万个客户端和每个客户端五个状态。还存在其他字段（例如状态更新日期和执行更新的用户），因此将表模式更改为简单Client, A, B是不可行的。

如何在子查询中引用分组字段？

mysql select

2 个回答

  1. 这行得通，可能还有更优雅的东西：

     insert 
 select 1 as client, 'A' as [status], 500 as 'Size'
 into #temp
 insert into #temp
 select 1 as client, 'B' as [status], 1500 as 'Size'
  insert into #temp
 select 2 as client, 'A' as [status], 2000 as 'Size'
 insert into #temp
 select 2 as client, 'B' as [status], 800 as 'Size'

 select t.client, ta.size as 'A', tb.size as 'B'
 from #temp as t
 INNER JOIN (select client, sum(size) as size from #temp where status = 'A' group by client) as ta on t.client = ta.client
 INNER JOIN (select client, sum(size) as size from #temp where status = 'B' group by client) as tb on t.client = tb.client
 group by t.client, ta.size, tb.size
    • 1
  2. Best Answer

    给你：

    SELECT
    Client,
    SUM(IF(Status = "A", Size, 0)) AS A,
    SUM(IF(Status = "B", Size, 0)) AS B
FROM yourtable
GROUP BY Client
    • 1

相关问题