SQL Server完全联接查询 [英] SQL server full join query

问题描述

我有一个完全连接的sql查询，我正在从同一张表中检索数据.

问题是我在期望列名的位置获取空值.

示例:
我有一个表，其中有两列typeOfPost，dob.

I have a full join sql query and I am retrieving the data from the same table.

The problem is I am getting the null value where I am expecting the column name.

Example:
I am having a table where there are two columns typeOfPost,dob.

DOB           TypeOfPost
---------    --------------
20/11/1998     Manager
1/1/2000       Sales
13/6/1999      Manager
20/1/1987      Manager
1/11/1985      Sales

现在，当我编写诸如
的联接查询时

Now when I am writing a join query like

select DATENAME(month,dob) as Red,count(TypeOfPost) 
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) as A

full join

select DATENAME(month,dob) as Green,count(TypeOfPost) 
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) as B on B.Green = A.Red

Output--                                Expected Output--
---------------------                   ---------------------
Month     Man     Sal                   Month     Man     Sal
--------  -----  ------                 --------  -----  ------
January    1       1                    January    1       1
NULL       1      NULL                  June       1      NULL
November   1       1                    November   1       1

现在出现问题了，我想在Month列中使用"June"而不是NULL值.
那有什么办法吗?
帮帮我.
谢谢.

Now here the problem rise, I want ''June'' in the column Month instead of NULL value.
So is there any way to get that?
Help me out.
Thanks.

推荐答案

我认为您需要在查询之前设置日期格式. SET DATEFORMAT dmy;
http://msdn.microsoft.com/en-us/library/ms189491.aspx [ ^ ]

I think you require to set date format before your query. SET DATEFORMAT dmy;
http://msdn.microsoft.com/en-us/library/ms189491.aspx[^]

select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob)

我制作了表格并将样本数据插入SSMS中并运行了该查询.执行后，我得到了您想要的输出:

I made the table and inserted sample data in SSMS and ran this query. After execution I got your desired output :

select Red month, man, sal from
(select DATENAME(month,dob) as Red,count(TypeOfPost) man
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) ) a
full join
(select DATENAME(month,dob) as Green,count(TypeOfPost) sal
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) ) b on B.Green = A.Red

输出为:

output was :

month                          man         sal
------------------------------ ----------- -----------
January                        1           1
June                           1           NULL
November                       1           1

我认为结果就是您想要的结果:-?
可以吗?

I think the result is the one you wanted :-?
Is it ok ?

select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob);

感谢prerak patel ..
干杯

Thanks to prerak patel..
Cheers

这篇关于SQL Server完全联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！