二个字段
A B
20091102 20091102
20091102 20091102
20091102 20091103
20091102 2009110420091103 20091103
20091103 20091103
20091103 2009110320091104 20091104
20091104 2009110520091105 20091106
....大概这样子的意思,如果A字段是某天,B字段也是当天,如果B字段不是当天则不再取值,以A字段 group 就得出当前完成了多少
20091102 是 50%
20091103 是 100%
20091104 是 50%
20091105 是 0%
...
以上的这样的SQL不知道如何写,请达人帮助一下,谢谢.
A B
20091102 20091102
20091102 20091102
20091102 20091103
20091102 2009110420091103 20091103
20091103 20091103
20091103 2009110320091104 20091104
20091104 2009110520091105 20091106
....大概这样子的意思,如果A字段是某天,B字段也是当天,如果B字段不是当天则不再取值,以A字段 group 就得出当前完成了多少
20091102 是 50%
20091103 是 100%
20091104 是 50%
20091105 是 0%
...
以上的这样的SQL不知道如何写,请达人帮助一下,谢谢.
SELECT X.A,'是',(SELECT COUNT(B) FROM TABLENAME Y WHERE Y.A=X.A)/COUNT(X.A) FROM TABLENAME X GROUP BY X.A
GO
CREATE TABLE TB(A VARCHAR(50),B VARCHAR(50))
INSERT INTO TB
SELECT '20091102','20091102' UNION ALL
SELECT '20091102','20091102' UNION ALL
SELECT '20091102','20091103' UNION ALL
SELECT '20091102','20091104' UNION ALLSELECT '20091103','20091103' UNION ALL
SELECT '20091103','20091103' UNION ALL
SELECT '20091103','20091103' UNION ALLSELECT '20091104','20091104' UNION ALL
SELECT '20091104','20091105' UNION ALLSELECT '20091105','20091106'SELECT
A+' 是 '+CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,2),SUM(CASE WHEN A=B THEN 1 ELSE 0 END)*100.0/COUNT(1) ))+'%'
FROM TB
GROUP BY A
/*
20091102 是 50.00%
20091103 是 100.00%
20091104 是 50.00%
20091105 是 0.00%
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(A VARCHAR(50),B VARCHAR(50))
INSERT INTO TB
SELECT '20091102','20091102' UNION ALL
SELECT '20091102','20091102' UNION ALL
SELECT '20091102','20091103' UNION ALL
SELECT '20091102','20091104' UNION ALLSELECT '20091103','20091103' UNION ALL
SELECT '20091103','20091103' UNION ALL
SELECT '20091103','20091103' UNION ALLSELECT '20091104','20091104' UNION ALL
SELECT '20091104','20091105' UNION ALLSELECT '20091105','20091106'SELECT
A+' 是 '+CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,2),SUM(CASE WHEN A=B THEN 1 ELSE 0 END)*100.0/COUNT(1) ))+'%'
FROM TB
GROUP BY A
/*
20091102 是 50.00%
20091103 是 100.00%
20091104 是 50.00%
20091105 是 0.00%
*/
2> go
A |B
----------|----------
20091102 |20091102
20091102 |20091102
20091102 |20091103
20091102 |20091104
20091103 |20091103
20091103 |20091103
20091103 |20091103
20091104 |20091104
20091104 |20091105
20091105 |20091106(10 rows affected)
1> select a,sum(case when a=b then 1 end)*1.00 / count(*) as p
2> from tb
3> group by a
4> go
a |p
----------|---------------------------
20091102 | .5000000000000
20091103 | 1.0000000000000
20091104 | .5000000000000
20091105 | NULL
1>
SELECT '2009-10-01','2009-10-01' UNION ALL
SELECT '2009-10-01','2009-10-01' UNION ALL
SELECT '2009-10-01','2009-10-03' UNION ALL
SELECT '2009-10-01','2009-10-03' UNION ALL
SELECT '2009-10-04','2009-10-04' UNION ALL
SELECT '2009-10-04','2009-10-04' UNION ALL
SELECT '2009-10-04','2009-10-04' UNION ALL
SELECT '2009-10-05','2009-10-05' UNION ALL
SELECT '2009-10-05','2009-10-06' UNION ALL
SELECT '2009-10-05','2009-10-05' UNION ALL
SELECT '2009-10-05','2009-10-05' SELECT X.A,'是',CAST(CAST((SELECT COUNT(B) FROM @TABLENAME Y WHERE Y.A=X.A
AND Y.B=X.A)*1.00/COUNT(X.A)*1.00*100 AS DECIMAL(19,2)) AS VARCHAR)+'%' FROM @TABLENAME X GROUP BY X.A
-- SORRY