列出没有groupby/aggregate的列的SQL查询

本文关键字：SQL 查询 aggregate groupby | 更新日期: 2023-09-27 18:04:34

我的两个表是这样的

购物表

buyer   timeArrived               billed     paid
ABC     2013-02-08 10:30:00       60$        45$
CTS     2013-02-18 10:30:00       100$       80$
ABC     2012-03-11 10:30:00       60$        40$
MNT     2012-02-08 10:30:00       100$       60$
CTS     2012-03-11 10:30:00       60$        40$

buyer_t表

buyer
ABC
MNT
CTS
GH

要搜索"最后一次见到"的买家，我从买家表查询到商店表像

SELECT
 v.buyer,
 MAX(s.timeArrived) AS LastSeen
 FROM buyer_t AS b
 INNER JOIN shop as s ON b.buyer = s.buyer
 where 
 l.buyer = 'ABC' or l.buyer = 'CTS'
 GROUP BY b.buyer;

知道ABC和CTS什么时候购物是很好的但是我如何获得"收费"answers"付费"专栏

列出没有groupby/aggregate的列的SQL查询

试试这个:

SELECT
 b.buyer,
 s.*
FROM buyer_t AS b
INNER JOIN shop as s ON b.buyer = s.buyer
INNER JOIN
(
   SELECT buyer, MAX(timeArrived) AS LastSeen
   FROM shop
   GROUP BY buyer
) AS s2  ON s.buyer = s2.buyer
        AND s.timeArrived = s2.lastSeen
WHERE s.buyer IN ('ABC', 'CTS');

SQL Fiddle Demo

这将给你:

| BUYER |                     TIMEARRIVED | BILLED | PAID |
-----------------------------------------------------------
|   ABC | February, 08 2013 10:30:00+0000 |    60$ |  45$ |
|   CTS | February, 18 2013 10:30:00+0000 |   100$ |  80$ |

或者:既然你使用的是SQL Server 2005，那么你可以使用排名函数ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)来做到这一点:

WITH CTE
AS
(
    SELECT
      s.*,
      ROW_NUMBER() OVER(PARTITION BY s.buyer
                        ORDER BY timeArrived DESC) AS Rownumber
    FROM buyer_t AS b
    INNER JOIN shop as s ON b.buyer = s.buyer
    WHERE s.buyer IN ('ABC', 'CTS')
) 
SELECT Buyer, TimeArrived, Billed, Paid
FROM CTE 
WHERE Rownumber = 1;
相关文章：