表和数据
ACCNO ID1
1111 0
1111 0
1111 1
2222 1
3333 0
4444 1
4444 0 结果 ACCNO DI2 ID3 ID4
1111 2 1 66.66%
2222 0 1 0%
3333 1 0 100%
4444 1 1 50% 产生结果方式: ACCNO为1111和ID1为0的总个数为2写入ID2
ACCNO为1111和ID1为1的总个数为1写入ID3
ACCNO为1111和ID1为0的总个数除以ACCNO为1111和ID为0,1的总个数为3写入ID4
依次 请问这个语句怎么写?
ACCNO ID1
1111 0
1111 0
1111 1
2222 1
3333 0
4444 1
4444 0 结果 ACCNO DI2 ID3 ID4
1111 2 1 66.66%
2222 0 1 0%
3333 1 0 100%
4444 1 1 50% 产生结果方式: ACCNO为1111和ID1为0的总个数为2写入ID2
ACCNO为1111和ID1为1的总个数为1写入ID3
ACCNO为1111和ID1为0的总个数除以ACCNO为1111和ID为0,1的总个数为3写入ID4
依次 请问这个语句怎么写?
accno,
id2=SUM(case when id1=0 then 1 else 0),
id3=SUM(case when id1=1 then 1 else 0),
id4=rtrim(cast((SUM(case when id1=0 then 1 else 0)*1.0/COUNT(*))*100 as decimal(8,2)))+'%'
from tb
group by accno
/***********************************************--> 测试数据:[TB]
--> 测试时间:2009-08-07 00:00:21
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[TB]') is not null drop table [TB]
create table [TB]([ACCNO] int,[ID1] int)
insert [TB]
select 1111,0 union all
select 1111,0 union all
select 1111,1 union all
select 2222,1 union all
select 3333,0 union all
select 4444,1 union all
select 4444,0select * from [TB]
select ACCNO,
DI2=sum(case when [ID1]=0 then 1 else 0 end),
ID3=sum(case when [ID1]=1 then 1 else 0 end),
ID4=sum(case when [ID1]=0 then 1 else 0 end)*1.00/count(1)
from TB group by ACCNO/*
ACCNO DI2 ID3 ID4
----------- ----------- ----------- ---------------------------------------
1111 2 1 0.6666666666666
2222 0 1 0.0000000000000
3333 1 0 1.0000000000000
4444 1 1 0.5000000000000(4 行受影响)*/drop table TB
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( ACCNO int,ID1 int)
go
insert tb SELECT
1111 , 0 UNION ALL SELECT
1111 , 0 UNION ALL SELECT
1111 , 1 UNION ALL SELECT
2222 , 1 UNION ALL SELECT
3333 , 0 UNION ALL SELECT
4444 , 1 UNION ALL SELECT
4444 , 0
go
select
accno,
id2=SUM(case when id1=0 then 1 else 0 end),
id3=SUM(case when id1=1 then 1 else 0 end ),
id4=rtrim(cast((SUM(case when id1=0 then 1 else 0 end )*1.0/COUNT(*))*100 as decimal(8,2)))+'%'
from tb
group by accno
go
/*
accno id2 id3 id4
----------- ----------- ----------- ------------------------------------------
1111 2 1 66.67%
2222 0 1 0.00%
3333 1 0 100.00%
4444 1 1 50.00%*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-07 00:08:31
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ACCNO] int,[ID1] int)
insert [tb]
select 1111,0 union all
select 1111,0 union all
select 1111,1 union all
select 2222,1 union all
select 3333,0 union all
select 4444,1 union all
select 4444,0
--------------开始查询--------------------------select
ACCNO,
ID2=sum(case when ID1=1 then 1 else 0 end),
ID3=sum(case when ID1=0 then 1 else 0 end),
ID4=rtrim(cast((sum(case when id1=0 then 1 else 0 end )*1.0/count(1))*100 as decimal(18,2)))+'%'
from
[tb]
group by
ACCNO
----------------结果----------------------------
/*
ACCNO ID2 ID3 ID4
----------- ----------- ----------- -----------------------------------------
1111 1 2 66.67%
2222 1 0 0.00%
3333 0 1 100.00%
4444 1 1 50.00%(所影响的行数为 4 行)*/
Drop table [tb]
Go
Create table [tb]([ACCNO] int,[ID1] int)
Insert tb
Select 1111,0 union all
Select 1111,0 union all
Select 1111,1 union all
Select 2222,1 union all
Select 3333,0 union all
Select 4444,1 union all
Select 4444,0
Go
--Select * from tb-->SQL查询如下:
select isnull(a.[ACCNO],b.[ACCNO]) [ACCNO],isnull(id2,0) id2,isnull(id3,0) id3,
ltrim(cast(isnull(id2,0)*1./(isnull(id2,0)+isnull(id3,0))*100 as dec(9,2)))+'%' id4
from (select [ACCNO],count(1) id2 from tb where id1=0 group by [ACCNO]) a
full join (select [ACCNO],count(1) id3 from tb where id1=1 group by [ACCNO]) b
on a.[ACCNO]=b.[ACCNO]
/*
ACCNO id2 id3 id4
----------- ----------- ----------- ------------------------------------------
1111 2 1 66.67%
2222 0 1 0.00%
3333 1 0 100.00%
4444 1 1 50.00%(4 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-07 00:08:31
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ACCNO] int,[ID1] int)
insert [tb]
select 1111,0 union all
select 1111,0 union all
select 1111,1 union all
select 2222,1 union all
select 3333,0 union all
select 4444,1 union all
select 4444,0
--------------开始查询--------------------------select
ACCNO,
ID2=sum(case when ID1=0 then 1 else 0 end),
ID3=sum(case when ID1=1 then 1 else 0 end),
ID4=rtrim(cast((sum(case when id1=0 then 1 else 0 end )*1.0/count(1))*100 as decimal(18,2)))+'%'
from
[tb]
group by
ACCNO
----------------结果----------------------------
/*ACCNO ID2 ID3 ID4
----------- ----------- ----------- -----------------------------------------
1111 2 1 66.67%
2222 0 1 0.00%
3333 1 0 100.00%
4444 1 1 50.00%(所影响的行数为 4 行)*/
declare @a table(accno varchar(12),id1 int)insert @a select
'1111', 0 union all select
'1111', 0 union all select
'1111', 1 union all select
'2222', 1 union all select
'3333', 0 union all select
'4444', 1 union all select
'4444', 0 SELECT *,DI4=CAST(CAST(DI2*1.00/((DI3+DI2)*1.00)*100 AS DECIMAL(6,2))AS VARCHAR(10))+'%'
FROM(
select accno,DI2=SUM(CASE WHEN ID1=0 THEN 1 ELSE 0 END),
DI3=SUM(CASE WHEN ID1=1 THEN 1 ELSE 0 END)
FROM @A GROUP BY ACCNO
)Taccno DI2 DI3 DI4
------------ ----------- ----------- -----------
1111 2 1 66.67%
2222 0 1 0.00%
3333 1 0 100.00%
4444 1 1 50.00%(4 行受影响)
create table [tb]([ACCNO] int,[ID1] int)
insert [tb]
select 1111,0 union all
select 1111,0 union all
select 1111,1 union all
select 2222,1 union all
select 3333,0 union all
select 4444,1 union all
select 4444,0
select accno,
id2=sum(case id1 when 0 then 1 else 0 end),
id3=sum(case id1 when 1 then 1 else 0 end),
id4=rtrim(cast(sum(case id1 when 0 then 1 else 0 end)*1.0/count(1)*100 as decimal(20,2)))+'%'
from tb
group by accno
create table [TB]([ISBN] int,[DWMC] varchar(4),[LB] varchar(1),[YS] varchar(3),[YW1] int,[LB1] varchar(1),[YS1] varchar(3),[YW11] int)
insert [TB]
select 123456,'杨昆','A','2M2',789,'E','A4',45 union all
select 3215,'杨昆','D','A4',34,'F','2M2',98select ISBN,DWMC,LB,YS,YW1 from [TB] union all --第一个结果集查询isbn=123456
select ISBN,DWMC,LB1,YS1,YW11 from [TB] order by isbn desc --第二个结果集查询ibsn=3215
-- 最后将两个结果用UNION ALL合并在一起,合并时两个SELECT列数必须相同
/*
ISBN DWMC LB YS YW1
123456 杨昆 A 2M2 789
123456 杨昆 E A4 45
3215 杨昆 F 2M2 98
3215 杨昆 D A4 34
*/
和刚才那个一样啊!
结贴没有if object_id('[TB]') is not null drop table [TB]
create table [TB]([ACCNO] int,[ID1] int)
insert [TB]
select 1111,0 union all
select 1111,0 union all
select 1111,1 union all
select 2222,1 union all
select 3333,0 union all
select 4444,1 union all
select 4444,0
select ACCNO,
DI2=sum(case when [ID1]=0 then 1 else 0 end),
ID3=sum(case when [ID1]=1 then 1 else 0 end),
ID4=ltrim(rtrim(cast(cast(sum(case when [ID1]=0 then 1 else 0 end)*1.00/count(1)*100 as decimal(14,2)) as varchar)))+'%'
from TB group by ACCNO
COUNT(CASE ID1 WHEN 1 THEN ACCNO END) AS DI3,
RTRIM(CONVERT(CHAR,CAST(COUNT(CASE ID1 WHEN 0 THEN ACCNO END)*100/COUNT(ACCNO) AS DECIMAL(5,2))))+'%' AS DI4
FROM DataInfo
GROUP BY ACCNO