通辽sql记录

SELECT * FROM TL.V_SEARCH_LOCATION_LIST WHERE ENTID='01';


SELECT * FROM TL.V_SEARCH_LOCATION_DETAIL WHERE LOCATION_ID='38';


SELECT * FROM TL.V_SEARCH_PROJECT_LIST WHERE LAND_ID='L01202104210004';


SELECT * FROM TL.V_SEARCH_BUILDER WHERE ID='SGF100001';

SELECT
TT.*, T4.BUILDER,
T4.LEGAL_PERSON,
T4.LEGAL_PERSON_PHONE,
(
CASE
WHEN T4.STATE = 0 THEN
'停用'
WHEN T4.STATE = 1 THEN
'启用'
ELSE
'未知'
END
) AS STATE1
FROM
(
WITH T AS (
SELECT DISTINCT
T1.FILENO,
T2.LANDID
FROM
TL.V_SEARCH_LOCATION_LIST T1,
TL.V_SEARCH_LOCATION_DETAIL T2
WHERE
T1.ENTID = '01'
AND T1."LOCATION" = T2.LOCATION_ID
AND T2.LANDID IS NOT NULL
) SELECT
T .LANDID,
T .FILENO,
T3.PROJECT_NAME,
T3.PLAN_START_DATE,
T3.PLAN_END_DATE,
T3.BUILDER,
(
CASE
WHEN T3.STATE = 0 THEN
'未开始'
WHEN T3.STATE = 1 THEN
'进行中'
WHEN T3.STATE = 2 THEN
'暂停'
WHEN T3.STATE = 3 THEN
'完成'
ELSE
'未知'
END
) AS STATE1
FROM
T,
TL.V_SEARCH_PROJECT_LIST T3
WHERE
T3.LAND_ID = T .LANDID
) TT,
TL.V_SEARCH_BUILDER T4
WHERE
T4. ID = TT.BUILDER;

 

 

 

 

 

 

 

SELECT
    TT.*, T4.BUILDER,
    T4.LEGAL_PERSON,
    T4.LEGAL_PERSON_PHONE,
    (
        CASE
        WHEN T4.STATE = 0 THEN
            '停用'
        WHEN T4.STATE = 1 THEN
            '启用'
        ELSE
            '未知'
        END
    ) AS STATE 
FROM
    (
        SELECT
            T2.LAND_ID,
            T1.FILENO,
            T2.PROJECT_NAME,
            T2.BUILDER BUILDERID,
            (
                CASE
                WHEN T2.STATE = 0 THEN
                    '未开始'
                WHEN T2.STATE = 1 THEN
                    '进行中'
                WHEN T2.STATE = 2 THEN
                    '暂停'
                WHEN T2.STATE = 3 THEN
                    '完成'
                ELSE
                    '未知'
                END
            ) AS STATE1,
            T2.PLAN_START_DATE,
            T2.PLAN_END_DATE
        FROM
            TL.V_SEARCH_LOCATION_LIST T1
        LEFT JOIN TL.V_SEARCH_PROJECT_LIST T2 ON (
            T2.LAND_ID LIKE '%' || T1. ID || '%'
            AND T1.FILENO IS NOT NULL
        )
        WHERE
            T1.ENTID = '02'
    ) TT
LEFT JOIN TL.V_SEARCH_BUILDER T4 ON T4. ID = TT.BUILDERID;

 

SELECT
    (CASE  WHEN zhuanghuoshuliang<=1000  THEN '1000吨以下'
           WHEN zhuanghuoshuliang<=2000 AND zhuanghuoshuliang>2  THEN '1000-2000吨'
           WHEN zhuanghuoshuliang<=4000 AND zhuanghuoshuliang>4  THEN '2000-4000吨'
           WHEN zhuanghuoshuliang<=8000 AND zhuanghuoshuliang>4  THEN '4000-8000吨'
           WHEN zhuanghuoshuliang>8000  THEN '8000吨以上'
           ELSE '其他' END) AS zhuanghuoshuliang,
    COUNT(zhuanghuoshuliang) AS zhuanghuoshuliangCount
FROM
    sourcelist
GROUP BY
    CASE  WHEN zhuanghuoshuliang<=1000  THEN '1000吨以下'
          WHEN zhuanghuoshuliang<=2000 AND zhuanghuoshuliang>2  THEN '1000-2000吨'
          WHEN zhuanghuoshuliang<=4000 AND zhuanghuoshuliang>4  THEN '2000-4000吨'
          WHEN zhuanghuoshuliang<=8000 AND zhuanghuoshuliang>4  THEN '4000-8000吨'
          WHEN zhuanghuoshuliang>8000  THEN '8000吨以上'
          ELSE '其他' END