假设我有三个表,分别是"用户"、"网桥"和"卡":
| ID | FirstName | LastName |
|----|------------|----------|
| 1 | David | Gray |
| 2 | John | Smith |
| ID | CardNumber |
|----|------------|
| 1 | 101 |
| 2 | 102 |
| CardNumber | Value |
|------------|-------|
| 101 | ABC |
| 101 | 1600 |
| 101 | 1700 |
| 101 | 1800 |
| 102 | DEF |
| 102 | 1699 |
| 102 | 1799 |
| 102 | 1899 |
我正在尝试编写执行以下操作的查询:
从表 1 中选择名字和姓氏,从表 2 中选择卡号,从表 3 中选择值,但我只希望每个用户有一个最高数字(旁注:我很乐意从卡中获取卡号,只要它匹配(
所以我的查询会返回
David, Gray, 101, 1800
John, Smith, 102, 1899
但是我无法弄清楚如何在MS-SQL中做到这一点。这是我到目前为止得到的:
SELECT
Users.ID,
Users.FirstName,
Users.LastName,
Bridge.CardNumber,
Cards.Value
FROM Users
LEFT JOIN Bridge ON Users.ID = Bridge.ID
LEFT JOIN Cards ON Bridge.CardNumber = Cards.CardNumber
WHERE
Cards.Value = (SELECT Cards.Value FROM Cards ORDER BY Cards.Value DESC)
返回单个用户,而不是所有用户。删除 WHERE
子句会像我预期的那样返回所有用户,但我不确定如何获得中间立场。
我在这里创建了一个SQLite小提琴,它足够接近(我可以从那里切换口味(
我能从这里做什么?
检查这个。具有分组依据和最大((函数的简单解决方案。
SELECT
Users.ID,
Users.FirstName,
Users.LastName,
Bridge.CardNumber,
MAX(cast(Cards.Value as int))
FROM Users
LEFT JOIN Bridge ON Users.ID = Bridge.Id
LEFT JOIN Cards ON Bridge.CardNumber = Cards.CardNumber
GROUP BY
Users.ID,
Users.FirstName,
Users.LastName,
Bridge.CardNumber
SQLite小提琴在这里,
试试这个...
select t1.ID,t1.FirstName,t1.LastName,t2.CardNumber,
max(cast(t3.Value as int)) as [MaxValue]
from tblUsers t1
inner join tblBridge t2 on t1.ID=t2.ID
inner join tblCards t3 on t2.CardNumber=t3.CardNumber
where isnumeric(t3.Value)=1
group by t1.ID,t1.FirstName,t1.LastName,t2.CardNumber
使用联接查询的每次迭代从卡片表中计算最大值并与查询绑定。
试试这个:
select t.*
from (
select Users.ID,
Users.FirstName,
Users.LastName,
Bridge.CardNumber,
Cards.Value,
row_number() over (
partition by Users.id order by convert(int, Cards.value) desc
) rn
from Users
left join Bridge on Users.ID = Bridge.ID
left join Cards on Bridge.CardNumber = Cards.CardNumber
and Cards.Value not like '%[^0-9]%'
) t
where rn = 1;
只需MAX(value)
并保持Group BY
图式:
CREATE TABLE #USERS( ID INT,FirstName VARCHAR(50),LastName VARCHAR(50))
INSERT INTO #USERS
SELECT 1, 'David' , 'Gray'
UNION ALL
SELECT 2, 'John' , 'Smith'
CREATE TABLE #BRIDGE( ID INT, CardNumber VARCHAR(50))
INSERT INTO #BRIDGE
SELECT 1 , 101
UNION ALL
SELECT 2 , 102
CREATE TABLE #CARDS( CardNumber VARCHAR(50), Value INT)
INSERT INTO #CARDS
SELECT 101, '1000'
UNION ALL
SELECT 101 , '1600'
UNION ALL
SELECT 101 , '1700'
UNION ALL
SELECT 101 , '1800'
UNION ALL
SELECT 102 , '800'
UNION ALL
SELECT 102 , '1699'
UNION ALL
SELECT 102 , '1799'
UNION ALL
SELECT 102 , '1899'
根据评论
不,它需要是一个数字,
SELECT U.FirstName, U.LastName, B.CardNumber,MAX( C.Value )
FROM #USERS U
INNER JOIN #BRIDGE B ON U.ID = B.ID
INNER JOIN #CARDS C ON B.CardNumber = C.CardNumber
GROUP BY U.FirstName, U.LastName, B.CardNumber
如果值是 varchar 类型并且具有类似 ABC 的值,并且您想要排除它们。
SELECT U.FirstName, U.LastName, B.CardNumber,MAX(CAST( C.Value AS INT) )
FROM #USERS U
INNER JOIN #BRIDGE B ON U.ID = B.ID
INNER JOIN #CARDS C ON B.CardNumber = C.CardNumber
WHERE ISNUMERIC(Value)=1
GROUP BY U.FirstName, U.LastName, B.CardNumber
结果将是
+-----------+----------+------------+-------+
| FirstName | LastName | CardNumber | value |
+-----------+----------+------------+-------+
| David | Gray | 101 | 1800 |
| John | Smith | 102 | 1899 |
+-----------+----------+------------+-------+
使用简单的子查询,我们也可以通过这种方式完成
select
T.FirstName,
T.LastName,
TT.CardNumber,
TTT.Value
from users T
INNER JOIN Bridge TT
ON T.ID = TT.ID
INNER JOIN (Select MAX(Value) Value ,
CardNumber
from Cards
WHERE CardNumber = CardNumber
AND VALUE LIKE '%[^A-Za-z]%'
GROUP BY CardNumber) TTT
ON TT.CardNumber = TTT.CardNumber
使用 LEFT JOIN
.并根据Value
列为Cards
表提供一个行号。
查询
select t1.FirstName, t1.LastName, t2.CardNumber, t3.Value
from Users t1
left join Bridge t2
on t1.ID = t2.ID
left join (
select [rn] = row_number() over(
partition by CardNumber
order by case when Value like '[0-9]%' then 1 else 2 end,
len(Value) desc, Value desc
), * from Cards) t3
on t2.CardNumber = t3.CardNumber
and t3.[rn] = 1;