从两个表中提取所有数据,从第三个表中提取单行



假设我有三个表,分别是"用户"、"网桥"和"卡":

| 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;

最新更新