合并 2 个 SQL 查询
我有 2 个查询,想在不使用联合的情况下合并到 1 个结果集中。
查询 1
select datepart(yy,dateclosed)as 'Year',
datepart(mm,dateclosed) as 'Month',
count(*)as 'Total'
from bug
where projectid = 44
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
group by datepart(yy,dateclosed), datepart(mm,dateclosed)
order by 1,2
查询 2
select datepart(yy,dateclosed)as 'Year',
datepart(mm,dateclosed) as 'Month',
count(*)as 'SameDay'
from bug
where projectid = 44
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
and CONVERT(VARCHAR(10), dateclosed, 101) = CONVERT(VARCHAR(10), datecreated, 101)
group by datepart(yy,dateclosed),datepart(mm,dateclosed)
order by 1,2
我希望它返回年、月、同日、总计等值。我该如何实现这一目标?联盟没有做我想要它做的事。我必须进行联接和表别名吗?子查询?
提前致谢。
I've got 2 queries I'd like to merge into 1 result set without using union.
Query 1
select datepart(yy,dateclosed)as 'Year',
datepart(mm,dateclosed) as 'Month',
count(*)as 'Total'
from bug
where projectid = 44
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
group by datepart(yy,dateclosed), datepart(mm,dateclosed)
order by 1,2
Query 2
select datepart(yy,dateclosed)as 'Year',
datepart(mm,dateclosed) as 'Month',
count(*)as 'SameDay'
from bug
where projectid = 44
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
and CONVERT(VARCHAR(10), dateclosed, 101) = CONVERT(VARCHAR(10), datecreated, 101)
group by datepart(yy,dateclosed),datepart(mm,dateclosed)
order by 1,2
Id like it to return the values as Year,Month,SameDay,Total. How do I achieve this? Union doesn't do what I want it to do. Do I have to do a join and a table alias? Subquery?
Thanks in advance.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
好吧……这个怎么样:
OK...what about this one:
试试这个:
Try this:
如果不使用联合,您可以创建一个临时表,将两个查询的结果写入其中,然后查询临时表。
我怀疑你不应该这样做,而应该弄清楚为什么工会不适合你。
你尝试了什么?
发生了什么?
Without using a union you could create a temporary table, write the results of the two queries into it and then query the temporary table.
I suspect that you shouldn't be doing this, but rather working out why the union isn't working for you.
What have you tried?
What happened?
你为什么要这样做?您是否知道您可以让查询返回多个结果集:
然后您的客户端可以独立读取 2 个结果集 - 这取决于您使用的语言,但在使用 SqlDataReader 的 C# 中您可以使用
NextResult
Why is it that you want to do this? Are you aware that you can just have the query return multiple result sets:
Your client can then read the 2 result sets independently - it depends on the langauge you use but in C# using SqlDataReader you use
NextResult