表和数据
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
依次 请问由表产生结果的语句该怎么写?
-- -----------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%*/
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 行)*/
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,
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
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/*
ACCNO DI2 ID3 ID4
1111 2 1 66.67%
2222 0 1 0.00%
3333 1 0 100.00%
4444 1 1 50.00%
*/
怎么发了两次