大佬教程收集整理的这篇文章主要介绍了选择查询缺少所需的结果，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
这里是重新创建场景的数据：
create table PAT (
name varchar(500),REFNO INT
);
create table MEDicATION (
REFNO INT,MEDicA INT
);
create table PERSONAL (
REFNO INT,PNnamE int
);
INSERT INTO PAT VALUES ('MANOJ',100)
INSERT INTO PAT VALUES ('SURESH',101)
INSERT INTO MEDicATION VALUES (100,1)
INSERT INTO MEDicATION VALUES (100,2)
INSERT INTO MEDicATION VALUES (101,3)
INSERT INTO MEDicATION VALUES (101,1)
INSERT INTO MEDicATION VALUES (101,5)
INSERT INTO PERSONAL VALUES (100,10)
INSERT INTO PERSONAL VALUES (100,11)
INSERT INTO PERSONAL VALUES (100,13)
INSERT INTO PERSONAL VALUES (101,10)
INSERT INTO PERSONAL VALUES (101,15)
预期输出：
我使用 ROW_numbER() 尝试过的：
SELECT name,PAT.REFNO,MEDicA,PNname FROM PAT
FulL JOIN (SELECT MEDicATION.*,ROW_numbER() OVER (PARTITION BY (REFNO) ORDER BY (SELECT NulL)) AS SEQNUM FROM MEDicATION) AS MEtable ON MEtable.REFNO = PAT.REFNO
FulL JOIN (SELECT PERSONAl.*,ROW_numbER() OVER (PARTITION BY (REFNO) ORDER BY (SELECT NulL)) AS SEQNUM FROM PERSONAL) AS PERSONALtable ON PERSONALtable.REFNO = PAT.REFNO AND PERSONALtable.SEQNUM = MEtable.SEQNUM
这给了我这个输出：
看起来你想要的是抑制数据和结果排序
使用 CASE 表达式检查 SEQNUM 并相应地显示 NAME 和 REFNO
SELECT NAME = CASE WHEN COALESCE(METABLE.SEQNUM,PERSONALTABLE.SEQNUM) = 1
THEN PAT.NAME END,REFNO = CASE WHEN COALESCE(METABLE.SEQNUM,PERSONALTABLE.SEQNUM) = 1
THEN PAT.REFNO END,MEDicA,PNNAME
FROM PAT
FULL JOIN
(
SELECT MEDicATION.*,ROW_numbER() OVER (PARTITION BY (REFNO)
ORDER BY (SELECT NULL)) AS SEQNUM
FROM MEDicATION
) AS METABLE ON METABLE.REFNO = PAT.REFNO
FULL JOIN
(
SELECT PERSONAl.*,ROW_numbER() OVER (PARTITION BY (REFNO)
ORDER BY (SELECT NULL)) AS SEQNUM
FROM PERSONAL
) AS PERSONALTABLE ON PERSONALTABLE.REFNO = PAT.REFNO
AND PERSONALTABLE.SEQNUM = METABLE.SEQNUM
ORDER BY COALESCE(PAT.REFNO,METABLE.REFNO,PERSONALTABLE.REFNO),COALESCE(METABLE.SEQNUM,PERSONALTABLE.SEQNUM)
对 import Head from 'next/head'
import Image from 'next/image'
import styles from '../styles/Home.module.css'
import { sayHello } from '@your-org/core-lib';
export default function Home() {
return (
<div className={styles.container}>
<Head>
<title>Create Next App</title>
<meta name="description" content="Generated by create next app" />
<link rel="icon" href="/favicon.ico" />
</Head>
<ul>
<li>{`Foo says: ${sayHello('World')} from @your-org/core-lib`}</li>
</ul>
</div>
和 CASE 列使用 NAME 表达式：
REFNO参见demo。
以上是大佬教程为你收集整理的选择查询缺少所需的结果全部内容，希望文章能够帮你解决选择查询缺少所需的结果所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。