‘壹’ sql语句：使用了union all后怎么分组排序
用子查询就能实现游改了
给你伏磨桐举个例子吧，要实现的功能你自己改
selecta,b,cfrom(
selecta,缺坦b,cfromaa
unionall
selecta1asa,b1asb,c1ascfrombb
)agroupbyc
‘贰’ 怎样修改多条sql子查询语句用union把结果集连接起来。
你这部分需要recursive query。
下面是 sample code， 具体逻辑需要你自己添进去，这个可以搜索无限深的层次。
http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO
‘叁’ SQL 如何将一个表中的两条或多条拥有相同ID的记录合并为一条
一、创建表：
create table stuUnion
(
sid int identity primary key,
cid int,
id varchar(500)
)
二、添加数据：
insert into stuUnion
elect 1,'a' union
select 1,'b' union
select 2,'c' union
select 2,'d' union
select 3,'e' union
select 3,'f' union
select 3,'g'
三、用标量函数查询：
创建标量函数：
create function b(@cid int)
returns varchar(500)
as
begin
declare @s varchar(500)
select @s=isnull(@s+'','')+rtrim(id)+',' from stuUnion where cid=@cid
return @s
end；
用标量函数查询：
select cid,dbo.b(cid) as id from stuUnion group by cid
用sqlserver的xml：
select cid,ID=STUFF((select ' '+rtrim(id)+',' from stuUnion where st.cid=cid order by id for XML path('')),1,1,'') from stuUnion st group by cid
‘肆’ sql追加查询中的UNION用法
insert into 月利润表 (门票收入,食品收入)
SELECT sum(日结算表.售票收入) AS 门票收入，0 AS 食品收入
FROM 日结算表
union
SELECT 0AS 门票收入， sum(商品日结算表.商品销售收入) AS 食品收入
FROM 商品日结算表;
这样试试，可以通过增加group 语句来合并成一行
‘伍’ 请教sql语句的用Union 后的group by 问题
select substring(comon,1,3) aa,count(*) bb
from (
select *
from table1
where xxx in (select xxx from tableA where...))
union
select *
from table1
where xxx in (select xxx from tableB where...))
) group by substring(comon,1,3)
‘陆’ sql语句查询，根据一个表中一个列，该列在两个不同条件同时满足的查询结果
1、在计算机中，打开Foxtable软件，新建一个表格，比如学生的评价成绩表，并输入数据，如下图所示。
‘柒’ sql server 中union的用法
select * from student 专业='计算机'
union
select * from student 专业='英语'
--上面这个命令中的union表示将两个select查询结果合并。
‘捌’ 关于SQL相同表分组排列组合的问题
建两个序列，并通过一个函数调用序列（union 不支持直接使用序列），用于排序。
先用第二组所有行（3行），union all 3遍第一组第一行（left join 第二组，行数就和第二组一样了）
在用第二组所有行（3行），union all 3遍第一组第二行（left join 第二组，行数就和第二组一样了）
最后按照序号，组别，编号排序
/*
dropsequenceBig_Letter1;
CreatesequenceBig_Letter1
Incrementby1
Startwith65
Maxvalue999999
Minvalue1
Nocycle
nocache;
dropsequenceBig_Letter2;
CreatesequenceBig_Letter2
Incrementby1
Startwith65
Maxvalue999999
Minvalue1
Nocycle
nocache;
--获取数列下一个值
createorreplacefunctionget_seq_next(seq_nameinvarchar2)returnnumber
is
seq_valnumber;
begin
executeimmediate'select'||seq_name||'.nextvalfromal'intoseq_val;
returnseq_val;
endget_seq_next;
*/
withtmpas(
select'1'groupid,'1'numfromal
union
select'1','2'fromal
union
select'2','1'fromal
union
select'2','2'fromal
union
select'2','3'fromal
)
selectchr(get_seq_next('Big_Letter1'))xuhao,t1.groupid,t1.num
fromtmpt1wheregroupid='2'
unionall
selectchr(get_seq_next('Big_Letter2'))xuhao,t1.groupid,t1.num
fromtmpt1,tmpt2
wheret1.groupid='1'andt1.num='1'andt2.groupid='2'
union
selectchr(get_seq_next('Big_Letter1'))xuhao,t1.groupid,t1.num
fromtmpt1wheregroupid='2'
unionall
selectchr(get_seq_next('Big_Letter2'))xuhao,t1.groupid,t1.num
fromtmpt1,tmpt2
wheret1.groupid='1'andt1.num='2'andt2.groupid='2'
orderbyxuhao,groupid,num
执行结果如下：
按照这个思路拼动态SQL吧