我想知道它是否足够向前,可以通过以下方式创建视图,我的尝试没有按预期工作,
我正在尝试使用以下查询创建另一个视图,当满足以下条件时,该查询标识有问题的"grp":
我对病情的想法的原始描述:
从控件中选择 GRP 的位置
('control_1.pvalue' ||' (2nd(' != 'control_2.pvalue'(
或
('control_1.pvalue' ||' (3rd(' != 'control_3.pvalue'(
或
('control_1.pvalue' ||' (4th(' != 'control_4.pvalue'(
或
('control_1.pvalue' ||' (5th(' != 'control_5.pvalue'(
或
('control_1.pvalue' ||' (6th(' != 'control_6.pvalue'(
我尝试了以下方法,期望返回M2:
SELECT grp FROM control_1 WHERE (pvalue || " (2nd)" != (SELECT pvalue from control_2) AND grp = (SELECT grp from control_2));
但即使是这个基本步骤也对我不起作用。
运行下面的代码片段以查看我尝试此操作的表和视图。
th, td {border: 1px solid black;}
<div>
<div>CREATE TABLE control (</div>
<div>id INTEGER PRIMARY KEY AUTOINCREMENT,</div>
<div>grp TEXT,</div>
<div>pname TEXT,</div>
<div>pvalue TEXT);</div>
<div></div><br />
<table>
<tr><td colspan="4"><div>control</div></td></tr>
<tr><th>id</th><th>grp</th><th>pname</th><th>pvalue</th></tr>
<tr><td>1</td><td>M1</td><td>d1</td><td>vat</td></tr>
<tr><td>2</td><td>M1</td><td>d2</td><td>vat (2nd)</td></tr>
<tr><td>3</td><td>M1</td><td>d3</td><td>vat (3rd)</td></tr>
<tr><td>4</td><td>M1</td><td>d4</td><td>vat (4th)</td></tr>
<tr><td>5</td><td>M1</td><td>d5</td><td>vat (5th)</td></tr>
<tr><td>6</td><td>M1</td><td>d6</td><td>vat (6th)</td></tr>
<tr><td>7</td><td>M2</td><td>d1</td><td>bin</td></tr>
<tr><td>8</td><td>M2</td><td>d2</td><td>ban (2nd)</td></tr>
<tr><td>9</td><td>M2</td><td>d3</td><td>bin (3rd)</td></tr>
<tr><td>10</td><td>M2</td><td>d4</td><td>bin (4th)</td></tr>
<tr><td>11</td><td>M2</td><td>d5</td><td>bin (5th)</td></tr>
<tr><td>12</td><td>M2</td><td>d6</td><td>bin (6th)</td></tr>
<tr><td>13</td><td>M3</td><td>d1</td><td>haw</td></tr>
<tr><td>14</td><td>M3</td><td>d2</td><td>haw (2nd)</td></tr>
<tr><td>15</td><td>M3</td><td>d3</td><td>ha (3rd)</td></tr>
<tr><td>16</td><td>M3</td><td>d4</td><td>haw (4th)</td></tr>
<tr><td>17</td><td>M3</td><td>d5</td><td>haw (5th)</td></tr>
<tr><td>18</td><td>M3</td><td>d6</td><td>haw (6th)</td></tr>
</table>
<div></div><br />
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d1";</div>
<div></div>
<table>
<tr><th colspan="4">control_1</th></tr>
<tr><th>id</th><th>grp</th><th>pname</th><th>pvalue</th></tr>
<tr><td>1</td><td>M1</td><td>d1</td><td>vat</td></tr>
<tr><td>7</td><td>M2</td><td>d1</td><td>bin</td></tr>
<tr><td>13</td><td>M3</td><td>d1</td><td>haw</td></tr>
</table>
<div></div><br />
<div></div>
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d2";</div>
<div></div>
<table>
<tr><th colspan="4">control_2</th></tr>
<tr><th>id</th><th>grp</th><th>pname</th><th>pvalue</th></tr>
<tr><td>2</td><td>M1</td><td>d2</td><td>vat (2nd)</td></tr>
<tr><td>8</td><td>M2</td><td>d2</td><td>ban (2nd)</td></tr>
<tr><td>14</td><td>M3</td><td>d2</td><td>haw (2nd)</td></tr>
</table>
<div></div><br />
<div>
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d3";</div>
<div></div>
<table>
<tr><th colspan="4">control_3</th></tr>
<tr><th>id</th><th>grp</th><th>pname</th><th>pvalue</th></tr>
<tr><td>3</td><td>M1</td><td>d3</td><td>vat (3rd)</td></tr>
<tr><td>9</td><td>M2</td><td>d3</td><td>bin (3rd)</td></tr>
<tr><td>15</td><td>M3</td><td>d3</td><td>ha (3rd)</td></tr>
</table>
<div></div><br />
<div>
<div>--I create the following as well, but we can see the problems from the 1st three views</div>
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d4";</div>
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d5";</div>
<div>CREATE VIEW control_1 AS SELECT * FROM control WHERE pname = "d6";</div>
</div>
我顿悟了,我能够弄清楚以下内容:
我创建了两个新视图,如下所示,现在我得到了我所期望的。
create view dview as
SELECT
DISTINCT
grp,
(select pvalue from control_1 WHERE control.grp == grp) as d1,
(select pvalue from control_2 WHERE control.grp == grp) as d2,
(select pvalue from control_3 WHERE control.grp == grp) as d3,
(select pvalue from control_4 WHERE control.grp == grp) as d4,
(select pvalue from control_5 WHERE control.grp == grp) as d5,
(select pvalue from control_6 WHERE control.grp == grp) as d6
FROM control;
下面是此新视图上的选择语句:
select * from dview;
grp d1 d2 d3 d4 d5 d6
---------- ---------- ---------- ---------- ---------- ---------- ----------
M1 vat vat (2nd) vat (3rd) vat (4th) vat (5th) vat (6th)
M2 bin ban (2nd) bin (3rd) bin (4th) bin (5th) bin (6th)
M3 haw haw (2nd) ha (3rd) haw (4th) haw (5th) haw (6th)
开始创建新视图:
create view dview_err as
SELECT * FROM dview WHERE
(d1 || " (2nd)" != d2) OR
(d1 || " (3rd)" != d3) OR
(d1 || " (4th)" != d4) OR
(d1 || " (5th)" != d5) OR
(d1 || " (6th)" != d6);
下面是此新视图上的选择语句:
select grp from dview_err;
grp
----------
M2
M3