/* Cable has a few hidden dangers
1, inquiry cable meter optic_cable,
2, according to the cable meter associated with the cable inspection (trajectory patrol) Route Chape (Optic_linepat trajectory of the route name), (Optic_linepat_sub, trajectory route of the standard Stone),
3, and then according to the standard stone related patrol information table of the stone all the hidden dangers (optic_linepat_rec patrol generated information table, according to the above query to the stone to match the hidden danger)
*/
Select a. Cable count as glcount,a. Cable length as gllength,b. Normal port as yesdk,b. Damaged port as NODK,
B. The business has used as yesyew,b. The business is not using as noyew,c. After the stone as biaoshi,d. Hidden danger as Yinhuan from
(SELECT COUNT (*) as ' cable strip ', SUM (CONVERT (int,cable_length)) as ' cable length ' from optic_cable) A,
(Select COUNT (case status is ' 0 ' then ' normal ' end) as ' normal Port ',
COUNT (case status is ' 1 ' then ' corrupted ' end) as ' damaged Port ',
COUNT (case when connect_content= "or connect_content is null then ' unused ' end) ' Business unused ',
COUNT (case when connect_content was not null and then ' used ' end) as ' business used '
From Optic_cable_port) b,
(select COUNT (c.f_id) ' through the standard Stone ' from optic_cable A/* Optical cable */
Left join Optic_linepat b/* cable patrol line */
On a.f_id=b.f_id
Left JOIN Optic_linepat_sub c/* Optical Cable path table (Standard stone) */
On b.f_id=c.f_olp_id
Where b.f_id is not null) C,
(Select COUNT (*) as ' hidden danger ' from optic_cable A/* Optical cable */
Left join Optic_linepat b/* cable patrol line (the name of a track patrol in the cable meter is associated with both) */
On a.f_id=b.f_id
Left JOIN Optic_linepat_sub c/* Optical Cable routing table (there is a marker in the number of track patrol bars, the link number of the track in the Stone table) */
On b.f_id=c.f_olp_id
The LEFT join Optic_linepat_rec d/* has the number of the stone in the Line patrol table f_well_id*/
On c.f_well_id =d.f_well_id/* take the stone to query the hidden trouble, the two associations * *
Where b.f_id is not null and d.f_env_state= ' hidden ') d
/** Query Total Records **/
Select A.glcount,a.gllength,b.duankouyes,b.duankouno,
B.yewuyes,b.yewuno, C.biaoshi, D. Hidden danger as Yinhuan from
(SELECT COUNT (*) as Glcount, sum (CONVERT (int,cable_length)) as Gllength from Optic_cable) A,
(Select COUNT (case status is ' 0 ' then ' normal ' end) as Duankouyes,
COUNT (case status is ' 1 ' then ' broken ' end) as Duankouno,
COUNT (case when connect_content= "or connect_content is null then ' not used ' end) Yewuno,
COUNT (case if connect_content is isn't null then ' used ' end) as Yewuyes
From Optic_cable_port) b,
(select COUNT (c.f_id) Biaoshi from Optic_cable a
Left JOIN Optic_linepat b
On a.f_id=b.f_id
Left JOIN Optic_linepat_sub C
On b.f_id=c.f_olp_id
Where b.f_id is not null) C,
(Select COUNT (*) as ' hidden danger ' from optic_cable a
Left JOIN Optic_linepat b
On a.f_id=b.f_id
Left JOIN Optic_linepat_sub C
On b.f_id=c.f_olp_id
Left JOIN Optic_linepat_rec D
On c.f_well_id =d.f_well_id
Where b.f_id is not null and d.f_env_state= ' hidden ') d
SQL query total records and query information about the table under each cable