数据核对

交付

1.交付数据全量：

SELECT count(*) from k_JfReceive where  jfDate < '2016-07-01 00:00:00.000';

2.交付去重（room）

SELECT count(T.A) from (SELECT DISTINCT roomguid  as A  from k_JfReceive where  jfDate < '2016-07-01 00:00:00.000') T ;
SELECT DISTINCT roomguid   from k_JfReceive where  jfDate < '2016-07-01 00:00:00.000'  ;

3.交付去重（contract）

SELECT count(T.A) from (SELECT DISTINCT ContractGUID  as A  from k_JfReceive where  jfDate < '2016-07-01 00:00:00.000') T ;
SELECT DISTINCT ContractGUID   from k_JfReceive where  jfDate < '2016-07-01 00:00:00.000'  ;

房产 

1.房产（签约的）

SELECT count(*) from p_Room where Status = '签约';

2.房产（签约 非车位）

SELECT
    COUNT (a.RoomGUID)
FROM
    p_Room a
WHERE
    a.status = '签约'
AND a.BProductTypeCode NOT IN (
    SELECT
        BProductTypeCode
    FROM
        p_BuildProductType
    WHERE
        BProducttypename LIKE '车位%'
);

3.房产（签约 非车位 有交付）

 1 SELECT
 2     COUNT (a.RoomGUID)
 3 FROM
 4     p_Room a
 5 WHERE
 6     a.status = '签约'
 7 AND a.BProductTypeCode not IN (
 8     SELECT
 9         BProductTypeCode
10     FROM
11         p_BuildProductType
12     WHERE
13         BProducttypename LIKE '车位%'
14 )
15 AND a.RoomGUID  IN (
16     SELECT DISTINCT
17         c.RoomGUID
18     FROM
19         k_JfReceive c where c.roomguid is not NULL
20 );
View Code

4.房产（签约 非车位 无交付）

 1 SELECT
 2     COUNT (a.RoomGUID)
 3 FROM
 4     p_Room a
 5 WHERE
 6     a.status = '签约'
 7 AND a.BProductTypeCode not IN (
 8     SELECT
 9         BProductTypeCode
10     FROM
11         p_BuildProductType
12     WHERE
13         BProducttypename LIKE '车位%'
14 )
15 AND a.RoomGUID  not IN (
16     SELECT DISTINCT
17         c.RoomGUID
18     FROM
19         k_JfReceive c where c.roomguid is not NULL
20 );
View Code

对比：

 1 SELECT '签约 非车位 无交付',
 2     COUNT (a.RoomGUID)
 3 FROM
 4     p_Room a
 5 WHERE
 6     a.status = '签约'
 7 AND a.BProductTypeCode not IN (
 8     SELECT
 9         BProductTypeCode
10     FROM
11         p_BuildProductType
12     WHERE
13         BProducttypename LIKE '车位%'
14 )
15 AND a.RoomGUID  not IN (
16     SELECT DISTINCT
17         c.RoomGUID
18     FROM
19         k_JfReceive c where c.roomguid is not NULL
20 )
21 
22 union ALL
23 SELECT '签约 非车位 有交付',
24     COUNT (a.RoomGUID)
25 FROM
26     p_Room a
27 WHERE
28     a.status = '签约'
29 AND a.BProductTypeCode not IN (
30     SELECT
31         BProductTypeCode
32     FROM
33         p_BuildProductType
34     WHERE
35         BProducttypename LIKE '车位%'
36 )
37 AND a.RoomGUID  IN (
38     SELECT DISTINCT
39         c.RoomGUID
40     FROM
41         k_JfReceive c where c.roomguid is not NULL
42 )
43 
44 UNION ALL
45 SELECT '签约 非车位',
46     COUNT (a.RoomGUID)
47 FROM
48     p_Room a
49 WHERE
50     a.status = '签约'
51 AND a.BProductTypeCode NOT IN (
52     SELECT
53         BProductTypeCode
54     FROM
55         p_BuildProductType
56     WHERE
57         BProducttypename LIKE '车位%'
58 );
View Code

签约 

1.（激活）

SELECT count(*) from s_Contract where Status = '激活';

2.(激活  时间 )

SELECT
    COUNT (*)
FROM
    s_Contract
WHERE
    Status = '激活'
AND QSDate < '2016-07-01 00:00:00.000';

3.签约（激活 时间 非车位）

SELECT
    COUNT (a.contractguid)
FROM
    s_contract a
JOIN p_Room b ON a.RoomGUID = b.RoomGUID
WHERE
    a.status = '激活'
AND a.QSDate < '2016-07-01 00:00:00.000'
AND b.BProductTypeCode NOT IN (
    SELECT
        BProductTypeCode
    FROM
        p_BuildProductType
    WHERE
        BProducttypename LIKE '车位%'
);
View Code

4.签约（有交付 激活 非车位 时间）

 1 SELECT
 2     COUNT (a.contractguid)
 3 FROM
 4     s_contract a
 5 JOIN p_Room b ON a.RoomGUID = b.RoomGUID
 6 WHERE
 7     a.status = '激活'
 8 AND a.QSDate < '2016-07-01 00:00:00.000'
 9 AND b.BProductTypeCode NOT IN (
10     SELECT
11         BProductTypeCode
12     FROM
13         p_BuildProductType
14     WHERE
15         BProducttypename LIKE '车位%'
16 )
17 AND a.contractguid IN (
18     SELECT
19         c.contractguid
20     FROM
21         k_JfReceive C
22     WHERE
23         c.contractguid IS NOT NULL
24 );
View Code

5.签约（无交付 激活 非车位 时间）

 1 SELECT
 2     COUNT (a.contractguid)
 3 FROM
 4     s_contract a
 5 JOIN p_Room b ON a.RoomGUID = b.RoomGUID
 6 WHERE
 7     a.status = '激活'
 8 AND a.QSDate < '2016-07-01 00:00:00.000'
 9 AND b.BProductTypeCode NOT IN (
10     SELECT
11         BProductTypeCode
12     FROM
13         p_BuildProductType
14     WHERE
15         BProducttypename LIKE '车位%'
16 )
17 AND a.contractguid NOT IN (
18     SELECT
19         c.contractguid
20     FROM
21         k_JfReceive C
22     WHERE
23         c.contractguid IS NOT NULL
24 );
View Code

6.签约（综合）

SELECT '激活',
    COUNT (*)
FROM
    s_Contract
WHERE
    Status = '激活'
UNION ALL
SELECT '激活 时间',
    COUNT (*)
FROM
    s_Contract
WHERE
    Status = '激活'
AND QSDate < '2016-07-01 00:00:00.000'
UNION ALL
SELECT '激活 时间 非车位',
    COUNT (a.contractguid)
FROM
    s_contract a
JOIN p_Room b ON a.RoomGUID = b.RoomGUID
WHERE
    a.status = '激活'
AND a.QSDate < '2016-07-01 00:00:00.000'
AND b.BProductTypeCode NOT IN (
    SELECT
        BProductTypeCode
    FROM
        p_BuildProductType
    WHERE
        BProducttypename LIKE '车位%'
)
UNION ALL
SELECT '有交付 激活 非车位 时间',
    COUNT (a.contractguid)
FROM
    s_contract a
JOIN p_Room b ON a.RoomGUID = b.RoomGUID
WHERE
    a.status = '激活'
AND a.QSDate < '2016-07-01 00:00:00.000'
AND b.BProductTypeCode NOT IN (
    SELECT
        BProductTypeCode
    FROM
        p_BuildProductType
    WHERE
        BProducttypename LIKE '车位%'
)
AND a.contractguid IN (
    SELECT
        c.contractguid
    FROM
        k_JfReceive C
    WHERE
        c.contractguid IS NOT NULL
)
union ALL
SELECT '无交付 激活 非车位 时间',
    COUNT (a.contractguid)
FROM
    s_contract a
JOIN p_Room b ON a.RoomGUID = b.RoomGUID
WHERE
    a.status = '激活'
AND a.QSDate < '2016-07-01 00:00:00.000'
AND b.BProductTypeCode NOT IN (
    SELECT
        BProductTypeCode
    FROM
        p_BuildProductType
    WHERE
        BProducttypename LIKE '车位%'
)
AND a.contractguid NOT IN (
    SELECT
        c.contractguid
    FROM
        k_JfReceive C
    WHERE
        c.contractguid IS NOT NULL
)
View Code

posted @ 2016-07-05 15:08  侠之大者kamil  阅读(139)  评论(0)    收藏  举报