谁能看明白这句sql是什么意思....好难啊~

Question

SELECT  Provinces.Pr_name, 
        CityRefer.cr_name, 
        BasicOffice.bo_name, 
        JdName.jd_name, 
        Equipment.sb_no, 
        ISNULL(derivedtbl_2.sumalldata, 0) AS sumalldata, 
        ISNULL(derivedtbl_2.suminperioddata, 0) AS suminperioddata, 
        Equipment.sb_state, 
        Equipment.sb_position, 
        Equipment.sb_person, 
        Equipment.sb_tel, 
        Equipment.sb_coordinates, 
        ISNULL(derivedtbl_2.meterscount, 0) AS meterscount, 
        ISNULL(derivedtbl_2.Alerted, 0) AS Alerted 
 FROM (
 SELECT MetersName_Ori.ConcentrationID, 
        COUNT(derivedtbl_1.mn_id) AS meterscount, 
        SUM(MetersName_Ori.AccuData) AS sumalldata, 
        SUM(derivedtbl_1.SumDataAdd) AS suminperioddata, 
        ISNULL(MAX(MetersName_Ori.Alerted), 0) AS Alerted 
  FROM (SELECT SumDataAdd, mn_id FROM dbo.MetersDataSumByPastMonth(0) AS MetersDataSumByPastMonth_1) 
  AS derivedtbl_1 
INNER JOIN MetersName_Ori ON derivedtbl_1.mn_id = MetersName_Ori.id 
GROUP BY MetersName_Ori.ConcentrationID) 
    AS derivedtbl_2 
    RIGHT OUTER JOIN Equipment ON derivedtbl_2.ConcentrationID = Equipment.sb_no 
    INNER JOIN BasicOffice ON Equipment.cr_id = BasicOffice.cr_id AND Equipment.bo_id = BasicOffice.bo_id AND Equipment.pr_id = BasicOffice.pr_id 
    INNER JOIN Provinces ON Equipment.pr_id = Provinces.Pr_id 
    INNER JOIN CityRefer ON Equipment.cr_id = CityRefer.cr_id AND Equipment.pr_id = CityRefer.pr_id 
    INNER JOIN JdName ON Equipment.pr_id = JdName.pr_id AND Equipment.cr_id = JdName.cr_id AND 
       Equipment.bo_id = JdName.bo_id AND Equipment.jd_id = JdName.jd_id

Answer 1

一个select 语句啊
难在业务关系 不在于语句本身。
你了解表结构和这个语句用途了就知道它是干什么的，别人没办法帮你。

Answer 2

这个SQL语句算是简单的了，你把代码按层次结构好（缩进处理），这样便于理解。

Answer 3

帮你整理一下，LZ如果知道这几个表示什么意思，大概就知道这个语句的含义了。 
SELECT  Provinces.Pr_name ,
        CityRefer.cr_name ,
        BasicOffice.bo_name ,
        JdName.jd_name ,
        Equipment.sb_no ,
        ISNULL(derivedtbl_2.sumalldata, 0) AS sumalldata ,
        ISNULL(derivedtbl_2.suminperioddata, 0) AS suminperioddata ,
        Equipment.sb_state ,
        Equipment.sb_position ,
        Equipment.sb_person ,
        Equipment.sb_tel ,
        Equipment.sb_coordinates ,
        ISNULL(derivedtbl_2.meterscount, 0) AS meterscount ,
        ISNULL(derivedtbl_2.Alerted, 0) AS Alerted
FROM    ( SELECT    MetersName_Ori.ConcentrationID ,
                    COUNT(derivedtbl_1.mn_id) AS meterscount ,
                    SUM(MetersName_Ori.AccuData) AS sumalldata ,
                    SUM(derivedtbl_1.SumDataAdd) AS suminperioddata ,
                    ISNULL(MAX(MetersName_Ori.Alerted), 0) AS Alerted
          FROM      ( SELECT    SumDataAdd ,
                                mn_id
                      FROM      dbo.MetersDataSumByPastMonth (0) AS MetersDataSumByPastMonth_1
                    ) AS derivedtbl_1
                    INNER JOIN MetersName_Ori ON derivedtbl_1.mn_id = MetersName_Ori.id
          GROUP BY  MetersName_Ori.ConcentrationID
        ) AS derivedtbl_2
        RIGHT OUTER JOIN Equipment ON derivedtbl_2.ConcentrationID = Equipment.sb_no
        INNER JOIN BasicOffice ON Equipment.cr_id = BasicOffice.cr_id
                                  AND Equipment.bo_id = BasicOffice.bo_id
                                  AND Equipment.pr_id = BasicOffice.pr_id
        INNER JOIN Provinces ON Equipment.pr_id = Provinces.Pr_id
        INNER JOIN CityRefer ON Equipment.cr_id = CityRefer.cr_id
                                AND Equipment.pr_id = CityRefer.pr_id
        INNER JOIN JdName ON Equipment.pr_id = JdName.pr_id
                             AND Equipment.cr_id = JdName.cr_id
                             AND Equipment.bo_id = JdName.bo_id
                             AND Equipment.jd_id = JdName.jd_id

Answer 4

SELECT Provinces.Pr_name,
       CityRefer.cr_name,
       BasicOffice.bo_name,
       JdName.jd_name,
       Equipment.sb_no,
       Isnull(derivedtbl_2.sumalldata, 0)      AS sumalldata,
       Isnull(derivedtbl_2.suminperioddata, 0) AS suminperioddata,
       Equipment.sb_state,
       Equipment.sb_position,
       Equipment.sb_person,
       Equipment.sb_tel,
       Equipment.sb_coordinates,
       Isnull(derivedtbl_2.meterscount, 0)     AS meterscount,
       Isnull(derivedtbl_2.Alerted, 0)         AS Alerted
FROM   (SELECT MetersName_Ori.ConcentrationID,
               Count(derivedtbl_1.mn_id)              AS meterscount,
               Sum(MetersName_Ori.AccuData)           AS sumalldata,
               Sum(derivedtbl_1.SumDataAdd)           AS suminperioddata,
               Isnull(Max(MetersName_Ori.Alerted), 0) AS Alerted
        FROM   (SELECT SumDataAdd,
                       mn_id
                FROM   dbo.Metersdatasumbypastmonth(0) AS MetersDataSumByPastMonth_1) AS derivedtbl_1
               INNER JOIN MetersName_Ori
                       ON derivedtbl_1.mn_id = MetersName_Ori.id
        GROUP  BY MetersName_Ori.ConcentrationID) AS derivedtbl_2
       RIGHT OUTER JOIN Equipment
                     ON derivedtbl_2.ConcentrationID = Equipment.sb_no
       INNER JOIN BasicOffice
               ON Equipment.cr_id = BasicOffice.cr_id
                  AND Equipment.bo_id = BasicOffice.bo_id
                  AND Equipment.pr_id = BasicOffice.pr_id
       INNER JOIN Provinces
               ON Equipment.pr_id = Provinces.Pr_id
       INNER JOIN CityRefer
               ON Equipment.cr_id = CityRefer.cr_id
                  AND Equipment.pr_id = CityRefer.pr_id
       INNER JOIN JdName
               ON Equipment.pr_id = JdName.pr_id
                  AND Equipment.cr_id = JdName.cr_id
                  AND Equipment.bo_id = JdName.bo_id
                  AND Equipment.jd_id = JdName.jd_id 
----5楼看来和我用的同样的第三方格式化工具。
能给个 CDKEY否？

Answer 5

我也来整理一下
SELECT  Provinces.Pr_name, CityRefer.cr_name, BasicOffice.bo_name, JdName.jd_name, Equipment.sb_no, ISNULL(derivedtbl_2.sumalldata, 0) AS sumalldata,
        ISNULL(derivedtbl_2.suminperioddata, 0) AS suminperioddata, Equipment.sb_state, Equipment.sb_position, Equipment.sb_person, Equipment.sb_tel,
        Equipment.sb_coordinates, ISNULL(derivedtbl_2.meterscount, 0) AS meterscount, ISNULL(derivedtbl_2.Alerted, 0) AS Alerted
FROM    (
          SELECT  MetersName_Ori.ConcentrationID, COUNT(derivedtbl_1.mn_id) AS meterscount, SUM(MetersName_Ori.AccuData) AS sumalldata,
                  SUM(derivedtbl_1.SumDataAdd) AS suminperioddata, ISNULL(MAX(MetersName_Ori.Alerted), 0) AS Alerted
          FROM    (
                    SELECT SumDataAdd, mn_id FROM dbo.MetersDataSumByPastMonth (0) AS MetersDataSumByPastMonth_1
                  ) AS derivedtbl_1
          INNER JOIN MetersName_Ori
          ON      derivedtbl_1.mn_id = MetersName_Ori.id
          GROUP BY MetersName_Ori.ConcentrationID
        ) AS derivedtbl_2
RIGHT OUTER JOIN Equipment
ON      derivedtbl_2.ConcentrationID = Equipment.sb_no
INNER JOIN BasicOffice
ON      Equipment.cr_id = BasicOffice.cr_id
        AND Equipment.bo_id = BasicOffice.bo_id
        AND Equipment.pr_id = BasicOffice.pr_id
INNER JOIN Provinces
ON      Equipment.pr_id = Provinces.Pr_id
INNER JOIN CityRefer
ON      Equipment.cr_id = CityRefer.cr_id
        AND Equipment.pr_id = CityRefer.pr_id
INNER JOIN JdName
ON      Equipment.pr_id = JdName.pr_id
        AND Equipment.cr_id = JdName.cr_id
        AND Equipment.bo_id = JdName.bo_id
        AND Equipment.jd_id = JdName.jd_id

Answer 6

SELECT后面跟的那些就是需要的，看字面意思也能知道个大概了
FROM后面跟的就可以不去看了，就是各种关系