gpt4 book ai didi

SQL Server 选择多个分组

转载 作者:行者123 更新时间:2023-12-02 07:38:16 25 4
gpt4 key购买 nike

我有两个描述用户及其付款的表:

CREATE TABLE test_users
(id int IDENTITY NOT NULL,
name varchar(25),
PRIMARY KEY (id));

CREATE TABLE test_payments
(id int IDENTITY NOT NULL,
user_id int NOT NULL,
money money NOT NULL,
date datetime NOT NULL,
PRIMARY KEY (id));

INSERT INTO test_users (name)
VALUES ('john');

INSERT INTO test_users (name)
VALUES ('peter');

INSERT INTO test_payments (user_id, money, date)
VALUES (1, $1, CONVERT(datetime, '15.12.2012'));

INSERT INTO test_payments (user_id, money, date)
VALUES (1, $2, CONVERT(datetime, '16.12.2012'));

INSERT INTO test_payments (user_id, money, date)
VALUES (2, $1, CONVERT(datetime, '16.12.2012'));

INSERT INTO test_payments (user_id, money, date)
VALUES (2, $3, CONVERT(datetime, '17.12.2012'));

INSERT INTO test_payments (user_id, money, date)
VALUES (1, $1, CONVERT(datetime, '19.12.2012'));

表 test_users:

id  name
-------------
1 john
2 peter

表 test_payments:

id  user_id money   last_activity
---------------------------------------
1 1 1.0000 2012-12-15
2 1 2.0000 2012-12-16
3 2 1.0000 2012-12-16
4 2 3.0000 2012-12-17
5 1 1.0000 2012-12-19

我需要做一个用户统计来显示:

  1. 用户名
  2. 一段时间内的总费用
  3. 最后一次的日期用户的事件(一般,不是一段时间)。

例如,以 15-18.12.12 期间为例,我希望得到以下结果:

name    total    last_activity
--------------------------------
peter $4 2012-12-17
john $3 2012-12-19

我试过以下查询:

SELECT u.*, SUM(p.money) total, MAX(p.date) last_activity
FROM test_users u
JOIN test_payments p
ON u.id= p.user_id
WHERE p.date BETWEEN CONVERT(datetime, '15.12.2012') AND CONVERT(datetime, '18.12.2012')
GROUP BY u.id, u.name
ORDER BY total DESC;

但是 last_activity 的结果是错误的,因为它也在日期范围内:

id  name    total   last_activity
--------------------------------
2 peter 4.0000 2012-12-17
1 john 3.0000 2012-12-16

请提出解决方案。

最佳答案

在我处理我的问题时,似乎弹出了其他几个答案,但无论如何它都在这里。这里有一个有效的 sql fiddle :http://sqlfiddle.com/#!3/14808/6

基本上,无论日期范围如何,您都需要一个查询来提取最大日期。我选择将其作为相关子查询来执行。

SELECT 
u.id,
u.name,
SUM(IsNull(money,0)) as TotalMoneyInRange,
(SELECT max(date) FROM test_payments where user_id = u.id) AS LastPaymentOverAll
FROM test_users AS u
LEFT JOIN test_payments AS p
ON u.id = p.user_id
WHERE
p.date IS NULL OR
p.date between
CAST('12-11-2012' AS datetime) --range begin
and
CAST('12-16-2012' as datetime) --range end
GROUP BY u.id, u.name

关于SQL Server 选择多个分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13976800/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com