T-SQL(Azure)仅显示2个结果，而不是3个 [英] T-SQL (Azure) Only shows 2 results instead of 3

问题描述

我正在扩展查询，但我遇到以下问题，我在一个表中有3个用户，他们与其他表连接(联接)，现在在这些表中，用户是否可以拥有数据，具体取决于他们动作.

Hi i am extending a query and i have the following problem, i have in a table 3 users and they are connected (joined) with other tables, now in these tables the user can or can not have data, depending on their actions.

SELECT 

COALESCE (credit.received, 0) AS CreditReceived, 
COALESCE (purchase.used, 0) AS CreditUsed, 
COALESCE (purchase.NumberOfPurchase, 0) AS NumberOfPurchase, 
COALESCE (credit.received, 0) - COALESCE (purchase.used, 0) AS UserCredit,

dbo.[User].id_user, 
dbo.[User].name, 
dbo.[User].town, 
dbo.[User].country

FROM (SELECT DISTINCT id_user
FROM dbo.UserCredit
UNION
SELECT DISTINCT id_user
FROM dbo.UserPurchase) AS users 

INNER JOIN
dbo.[User] ON users.id_user = dbo.[User].id_user 

LEFT OUTER JOIN
(SELECT        
id_user, 
SUM(creditRecieved) AS received
FROM            
dbo.UserCredit AS UserCredit_1
GROUP BY id_user) AS credit 
ON users.id_user = credit.id_user 

LEFT OUTER JOIN
(SELECT        
id_user, 
SUM(creditUsed) AS used,
COUNT(creditUsed) AS NumberOfPurchase
FROM            
dbo.UserPurchase AS UserPurchase_1
GROUP BY id_user) AS purchase 
ON users.id_user = purchase.id_user

信息来自这些表；

表dbo.user

id_user     name        town           country
----------- ----------- -------------- -------------
1           George      New York       USA
2           Lucas       San Diego      GB
3           Steven      San Fran       Germany

表dbo.UserCredit

TABLE dbo.UserCredit

id          id_user     creditRecieved PurchasePrice
----------- ----------- -------------- -------------
1           1           150            750
2           1           25             100
3           2           65             15

表dbo.UserPurchase

TABLE dbo.UserPurchase

id          id_user     creditUsed  date
----------- ----------- ----------- -----------
1           1           175         NULL
2           2           3           NULL
3           2           2           NULL

我得到的只是两个第一个user_id的结果，而第三个(steven)的结果未显示，我想这是因为该id在其他两个表中不存在，但是我希望用COALESCE修复，并且将其中四个设置为0.我在做什么错了?

What i get is only the result of the two first user_id's and the third one (steven) is not shown in the results, i guess it is because the id's dont exist in the other two tables, but that i was hoping would be fixed with the COALESCE and the four of them would be set to 0. What am i doing wrong?

推荐答案

通过将第一个DISTINCT替换为dbo.[User]而不是dbo.UserCredit，它给了我正确的结果；感谢COALESCE:)

By replacing the first DISTINCT with the dbo.[User] instead of the dbo.UserCredit it gave me the right results; All users in the overview with credit or no credit, thanks to the COALESCE :)

SELECT 

COALESCE (credit.received, 0) AS CreditReceived, 
COALESCE (purchase.used, 0) AS CreditUsed, 
COALESCE (purchase.NumberOfPurchase, 0) AS NumberOfPurchase, 
COALESCE (credit.received, 0) - COALESCE (purchase.used, 0) AS UserCredit,

dbo.[User].id_user, 
dbo.[User].name, 
dbo.[User].town, 
dbo.[User].country

FROM (SELECT DISTINCT id_user
FROM dbo.[User]
UNION
SELECT DISTINCT id_user
FROM dbo.UserPurchase) AS users 

INNER JOIN
dbo.[User] ON users.id_user = dbo.[User].id_user 

LEFT OUTER JOIN
(SELECT        
id_user, 
SUM(creditRecieved) AS received
FROM            
dbo.UserCredit AS UserCredit_1
GROUP BY id_user) AS credit 
ON users.id_user = credit.id_user 

LEFT OUTER JOIN
(SELECT        
id_user, 
SUM(creditUsed) AS used,
COUNT(creditUsed) AS NumberOfPurchase
FROM            
dbo.UserPurchase AS UserPurchase_1
GROUP BY id_user) AS purchase 
ON users.id_user = purchase.id_user

这篇关于T-SQL(Azure)仅显示2个结果，而不是3个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！