(1) head or the tail is less than 1=unqualified
(2) the head, tail, between any two consecutive points is less than 1=unqualified
Otherwise qualified
Determine whether a surface qualified logic:
There are two or more than two three data is unqualified unqualified
The create table # A (
Id varchar (200), - don't
Iid int - face other each
Fz1 varchar (50),
Fz2 varchar (50),
Fz3 varchar (50),
Fz4 varchar (50),
Fz5 varchar (50),
Fz6 varchar (50),
Fz7 varchar (50),
Fz8 varchar (50),
Fz9 varchar (50),
Fz10 varchar (50),
Fz11 varchar (50),
Fz12 varchar (50),
Iidishg varchar (50), - whether each below each qualified
Idishg varchar (50), the whole face whether qualified
)
Insert into # A (id iid, fz1, fz2, fz3, fz4, fz5, fz6, fz7, fz8, fz9, fz10, fz11, fz12, iidishg, idishg)
Values
(' negative ', '1', '4.53', 5.67 ' ', '5.08', 4.25 ' ', '3.7', 0.34 ' ', ', ', ', ', ', ', ', '),
(' negative ', '2', '4.76', 5.67 ' ', '4.32', 4.06 ' ', '4.17', 4.8 ' ', ', ', ', ', ', ', ', '),
(' negative ', '3', '0.22', 4.6 ' ', '4.84', 4.33 ' ', '1.14', 5.6 ' ', ', ', ', ', ', ', ', '),
(' positive ', '1', '6.67', 4.84 ' ', '6.22', 5.2 ' ', '4.14', 5.76 ' ', '5.8', 4.38 ' ', '0.55', 6.96 ' ', '5.44', 5.6 ' ', ', '),
(' positive ', '2', '6.18', 5.85 ' ', '4.13', 0.19 ' ', '4.25', 4.77 ' ', '5.03', 0.32 ' ', '4.47', 5.67 ' ', '5.64', 5.21 ' ', ', '),
(' positive ', '3', '6.41', 4.36 ' ', '0.22', 4.45 ' ', '4.06', 8.61 ' ', '5.42', 0.24 ' ', '0.29', 6.28 ' ', '4.38', 9.24 ' ', ', ')
Select * from # A
The effect of the need to implement:
Bosses know how can realize
CodePudding user response:
Have bosses have a more simple way, at present my method is
Select id iid, fz1 fz2, fz3, fz4, fz5, fz6, fz7, fz8, fz9, fz10, fz11, fz12, lv, REPLACE (lv, 2, ' ') as ylv, left (the REPLACE (lv, 2, ' '), 1) as the head, right (REPLACE (lv, 2, ' '), 1) as tail,
Right (left (the REPLACE (lv, 2, ' '), len (REPLACE (lv, 2, ' ')) - 1), len (left (the REPLACE (lv, 2, ' '), len (REPLACE (lv, 2, ' ')) - 1)) - 1) as middle,
The case when the left (the REPLACE (lv, 2, ' '), 1)=1 or right (REPLACE (lv, 2, ' '), 1)=1 then 'unqualified'
When the left (the REPLACE (lv, 2, ' '), 1)=0 and right (REPLACE (lv, 2, ' '), 1)=0 and
Right (left (the REPLACE (lv, 2, ' '), len (REPLACE (lv, 2, ' ')) - 1), len (left (the REPLACE (lv, 2, ' '), len (REPLACE (lv, 2, ' ')) - 1)) - 1) like '% 11%' then 'unqualified'
The else 'qualified' end as iidishg
Into # B
The from
(select *, lv=(case when cast (fz1 as float) & lt; 1 then '1' else '0' end) +
(case when fz2="' then '2' when cast (fz2 as float) & lt; 1 then '1' else '0' end) +
(case when fz3="' then '2' when cast (fz3 as float) & lt; 1 then '1' else '0' end) +
(case when fz4="' then '2' when cast (fz4 as float) & lt; 1 then '1' else '0' end) +
(case when fz5="' then '2' when cast (fz5 as float) & lt; 1 then '1' else '0' end) +
(case when fz6="' then '2' when cast (fz6 as float) & lt; 1 then '1' else '0' end) +
(case when fz7="' then '2' when cast (fz7 as float) & lt; 1 then '1' else '0' end) +
(case when fz8="' then '2' when cast (fz8 as float) & lt; 1 then '1' else '0' end) +
(case when fz9="' then '2' when cast (fz9 as float) & lt; 1 then '1' else '0' end) +
(case when fz10="' then '2' when cast (fz10 as float) & lt; 1 then '1' else '0' end) +
(case when fz11="' then '2' when cast (fz11 as float) & lt; 1 then '1' else '0' end) +
(case when fz12="' then '2' when cast (fz12 as float) & lt; 1 then '1' else '0' end) from # A
) t
The select Anderson d, iid, fz1 fz2, fz3, fz4, fz5, fz6, fz7, fz8, fz9, fz10, fz11, fz12, iidishg, case when isnull (b.i dishg, 0) & gt;=2 then 'unqualified' else 'qualified' end as isishg
The from # a, B
Left the join (select id, count (iidishg) as idishg from # B where iidishg='unqualified group by id) B on Anderson, d=b.i d
CodePudding user response:
/*
Use your data
First query your 12 columns into 12 lines, and add a serial number column (x, y)
And then calculate the biggest an id value is not empty (ma)
And then calculate the value of the offset forward a used to judge the adjacent la
Then there is judgment results RST
The first (x=1 AND y<1.0)
The last one (x=ma AND y<1.0)
Adjacent (y<1.0 the AND la<1.0)
Then press the id group statistics the number of unqualified get rst2
*/
SELECT *, IIF (SUM (IIF (RST='unqualified, 1, 0)) OVER (PARTITION BY id) & gt;=2, 'unqualified', 'qualified') AS rst2 FROM # A OUTER APPLY
(
The SELECT MIN (IIF ((x=1 AND y<1.0) OR (x=ma AND y<1.0) OR (y<1.0 the AND la<1.0), 'unqualified', 'qualified')) AS RST
The FROM
(
SELECT
X,
Y,
MAX (x) OVER (PARTITION BY id, iid) AS ma,
LAG (y) OVER (PARTITION BY id iid ORDER BY x) AS la
The FROM (
VALUES
(fz1, 1), (2, fz2), (3, fz3), (4, fz4), (5, fz5), (6, fz6), (7, fz7), (8, fz8), (9, fz9), (10, fz10), (11, fz11), (12, fz12)
) AS a (x, y)
WHERE y<> '
) a
B)
CodePudding user response:
Looking is quite complicated
With cte_1
As
(select id iid, cast (fz_value as a decimal (12, 2)) as fz_value, cast (replace (fz_seq, 'fz', ') as int) as fz_seq,
Count (*) over (partition by id, iid) as fz_qty
The from
(select id iid, fz_value, fz_seq
The from # A
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull