已知2列数据的记录(用‘*’表示2列数据的间隔)
BL1QY BL2QY
4 * 3
1 * 4
1 * 2
2 * 4
2 * 3
3 * 2
4 * 1
4 * 3
1 * 4
1 * 3
1 * 2
3 * 3
2 * 1
2 * 3
3 * 4
3 * 4
3 * 4
2 * 1
4 * 2
1 * 4
1 * 2
3 * 2
4 * 2
4 * 1
2 * 2
1 * 4
2 * 2
3 * 2
1 * 3
2 * 3
2 * 4
4 * 3
3 * 4
2 * 3
如何用SQL语句统计记录数据重复的次数SAME
BL1QY * BL2QY SAME
4 * 3
1 * 4
1 * 2
2 * 4
2 * 3
3 * 2
4 * 1
1 * 3
3 * 3
2 * 1
3 * 4
4 * 2
2 * 2
1 * 1
3 * 1
4 * 4
BL1QY BL2QY
4 * 3
1 * 4
1 * 2
2 * 4
2 * 3
3 * 2
4 * 1
4 * 3
1 * 4
1 * 3
1 * 2
3 * 3
2 * 1
2 * 3
3 * 4
3 * 4
3 * 4
2 * 1
4 * 2
1 * 4
1 * 2
3 * 2
4 * 2
4 * 1
2 * 2
1 * 4
2 * 2
3 * 2
1 * 3
2 * 3
2 * 4
4 * 3
3 * 4
2 * 3
如何用SQL语句统计记录数据重复的次数SAME
BL1QY * BL2QY SAME
4 * 3
1 * 4
1 * 2
2 * 4
2 * 3
3 * 2
4 * 1
1 * 3
3 * 3
2 * 1
3 * 4
4 * 2
2 * 2
1 * 1
3 * 1
4 * 4
from(
select case when bl1qy>bl2qy then bl1qy else bl2qy end A,
case when bl1qy>bl2qy then bl2qy else bl1qy end B
from tb
)T
group by A+'*'+B
A B
4 * 3 ---统计4 3重复了多少次
1 * 4 ---统计1 4重复了多少次
1 * 2 ---统计1 2重复了多少次
2 * 4 ---以此类推.....
2 * 3
3 * 2
4 * 1
4 * 3
1 * 4
1 * 3
1 * 2
3 * 3
2 * 1
2 * 3
3 * 4
3 * 4
3 * 4
2 * 1
4 * 2
1 * 4
1 * 2
3 * 2
4 * 2
4 * 1
2 * 2
1 * 4
2 * 2
3 * 2
1 * 3
2 * 3
2 * 4
4 * 3
3 * 4
2 * 3
use tempdb;
go
create table dbo.tb
(
id int,
value int
);
insert into dbo.tb
select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 2,3
union all select 3,2
union all select 4,1
union all select 1,3
union all select 3,3
union all select 2,1
union all select 3,4
union all select 4,2
union all select 2,2
union all select 3,1
union all select 4,4
union all select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 3,2
union all select 4,1
union all select 1,3
union all select 2,1
union all select 3,4
union all select 2,2
union all select 4,4 select id,value,COUNT(*)over(partition by id,value)as cis
from dbo.tb;/*
id value cis
----------- ----------- -----------
1 2 2
1 2 2
1 3 2
1 3 2
1 4 2
1 4 2
2 1 2
2 1 2
2 2 2
2 2 2
2 3 1
2 4 2
2 4 2
3 1 1
3 2 2
3 2 2
3 3 1
3 4 2
3 4 2
4 1 2
4 1 2
4 2 1
4 3 2
4 3 2
4 4 2
4 4 2(26 行受影响)
*/
[A][B]
4 * 3 ---统计4 3重复了多少次
1 * 4 ---统计1 4重复了多少次
1 * 2 ---统计1 2重复了多少次
2 * 4 ---以此类推.....
2 * 3
3 * 2
4 * 1
4 * 3
1 * 4
1 * 3
1 * 2
3 * 3
2 * 1
2 * 3
3 * 4
3 * 4
3 * 4
2 * 1
select id,value,COUNT(*)as cis
from dbo.tb
group by id,value;
[A][B]
1 * 2 ---统计4 3重复了多少次
1 * 3 ---统计1 4重复了多少次
1 * 4 ---统计1 2重复了多少次
2 * 5 ---以此类推.....
1 * 2
1 * 2
1 * 2
1 * 3
1 * 4
1 * 3
1 * 2
1 * 3
1 * 4
1 * 3
1 * 4
1 * 4
1 * 4
2 * 5
执行SQL语句后的结果为
[A][B][SAME]
1 * 2 *5---统计1 2在数据表[TB22]重复了5次
1 * 3 *5---统计1 3在数据表[TB22]重复了5次
1 * 4 *2---统计1 4在数据表[TB22]重复了5次
2 * 5 *2---以此类推.....
1 * 2*5
1 * 2*5
1 * 2*5
1 * 3*5
1 * 4*2
1 * 3*5
1 * 2*5
1 * 3*5
1 * 4*6
1 * 3*5
1 * 4*6
1 * 4*6
1 * 4*6
2 * 5*2请问如何用SQL语句完成
已知数据表[TB22]里有2列数据[A],[B]的记录(用‘*’表示2列数据的间隔,表里没有*)
[A][B]
1 * 2 ---统计1 2重复了多少次
1 * 3 ---统计1 3重复了多少次
1 * 4 ---统计1 4重复了多少次
2 * 5 ---以此类推.....
1 * 2
1 * 2
1 * 2
1 * 3
1 * 4
1 * 3
1 * 2
1 * 3
1 * 4
1 * 3
1 * 4
1 * 4
1 * 4
2 * 5
执行SQL语句后的结果为
[A][B][SAME]
1 * 2 *5---统计1 2在数据表[TB22]重复了5次
1 * 3 *5---统计1 3在数据表[TB22]重复了5次
1 * 4 *2---统计1 4在数据表[TB22]重复了5次
2 * 5 *2---以此类推.....
1 * 2*5
1 * 2*5
1 * 2*5
1 * 3*5
1 * 4*2
1 * 3*5
1 * 2*5
1 * 3*5
1 * 4*6
1 * 3*5
1 * 4*6
1 * 4*6
1 * 4*6
2 * 5*2请问如何用SQL语句完成
SELECT DISTINCT m.BG_Etype,m.BGID,m.BG_TName,m.BG_Freight,m.BG_DCharge
FROM(
SELECT DISTINCT top 100 a.BG_Etype,a.BGID,b.BG_TName,e.BG_Freight
,b.BG_DCharge,e.bg_stime,e.BG_Time,a.BG_BMode,(CASE
WHEN d.OG_Type=3 THEN c.OG_Rate_A*e.BG_Freight WHEN d.OG_Type=4 THEN c.OG_Rate_C*e.BG_Freight
WHEN d.OG_Type=5 THEN c.OG_Rate_E*e.BG_Freight ELSE 0 END) a,
b.BG_ASummer,b.BG_BSummer,b.BG_CSummer
FROM BG_Bargain b
INNER JOIN BB_Bargain e ON b.BGID=e.BGID
INNER JOIN BA_Bargain a ON a.BGID=b.BGID
INNER JOIN XT_Organ c ON b.BG_SNode=c.OGID
INNER JOIN XT_Organ d ON b.BG_TNode=d.OGID
WHERE (BG_BG_Type=2 AND e.BG_Freight<>0 OR (a.BG_BMode=1 AND b.BG_BMode=0))
AND BJ_Balance IS NULL AND e.BG_Time>='2010-09-26 08:00:00'
AND e.BG_Time<'2010-10-26 08:00:00' AND e.BG_SNode='100520' order by e.bg_time desc
) m
m.xx ,m.xb这样只能点出一个表中的数据,a.BG_BMode是原始表中的,b.BG_BMode是变更以后
请问外面一层能跨表显示列不?(a.BG_BMode=1 AND b.BG_BMode=0),a,b完全是两个表中的数据。。
--统计tb1中 a,b 在tb2中重复的次数
use tempdb;
go
create table dbo.tb1
(
a int,
b int
);
create table dbo.tb2
(
a int,
b int
);
insert into dbo.tb2
select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 2,3
union all select 3,2
union all select 4,1
union all select 1,3
union all select 3,3
union all select 2,1
union all select 3,4
union all select 4,2
union all select 2,2
union all select 3,1
union all select 4,4
union all select 4,3
union all select 1,4
union all select 1,2
union all select 2,4
union all select 3,2
union all select 4,1
union all select 1,3
union all select 2,1
union all select 3,4
union all select 2,2
union all select 4,4 select a,b,(select count(*)
from dbo.tb2
where CAST(a as varchar(2))+CAST(b as varchar(2))=
CAST(t1.a as varchar(2))+CAST(t1.b as varchar(2)))as cs
from dbo.tb1 as t1;
/*
a b cs
----------- ----------- -----------
4 3 2
1 4 2
1 2 2
2 4 2
2 3 1
3 2 2
4 1 2
1 3 2
3 3 1
2 1 2
3 4 2
4 2 1
2 2 2
3 1 1
4 4 2
4 3 2
1 4 2
1 2 2(18 行受影响)*/
--区别很大吗
select id,value,COUNT(*)over(partition by id,value)as cis
from dbo.tb;select ID
,[BL1QY]
,[BL2QY]
,COUNT(*)over(partition by [BL1QY],[BL2QY])as SAME
from [DBO].[TB22]
ORDER BY IDselect id,value,COUNT(*)as cis
from dbo.tb
group by id,value;
,[BL1QY]
,[BL2QY]
,COUNT(*)over(partition by [BL1QY],[BL2QY])as SAME
from [DBO].[TB22]
ORDER BY ID是符合要求的,看来17楼是理解上的一点点偏差,谢谢17楼