解决方案 »
- 存储过程中如何在一个表插入属性?
- openrowset 查询vfp数据库出现的问题,有自增字段就不行??
- ---------给我看下这个功能用一个SELECT语句查询出来吗--------
- 触发器里不能使用链接服务器(LinkedServer)吗?
- 50分急救
- SQL Server进程问题
- 如何实现两个SQL Server数据库服务器之间的数据交叉同步??????????
- 谁能给各sql 2005 的技术白皮书?讲的要深入的,e文的,中文的
- 在SQLSERVER中如何使用块状游标,提取多行数据!
- 请教一下sqlserver的事物控制和锁机制
- 在FireBrid中用一个表去更新另一个表的问题。
- SQL Server 2008事务复制在正常运行,如何将新表加到复制中?
我不是很懂,要怎么样才能关联一次那,在inner join一次?可是那样也不对啊
我现在就是想在approval(这个是审批表)表中把申请人去出来(也就是apply中userId关联的name)如果我把apply当作主表(也就from apply) ,我的查询结果中的上一次审批时间就查询不出来,我应该怎么样做那??
已经inner join了,直接取就可以了啊
inner join users as users2 on approval.userid=users2.id然后之前的申请人替换为 users2.name as 申请人
select apply.id as 申请编号,config.name as 配置节名称,apply.userId as 申请人,
apply.time as 申请时间,approval.time as 上一次审批时间,users.name as 审批人,
apply.state as 状态,apply.type as 申请类型 from approval
inner join users on approval.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId改之后的结果
但是申请人和审批人一样了啊
下面是apply表
approval表
apply.time as 申请时间,approval.time as 上一次审批时间,users.name as 审批人,
apply.state as 状态,apply.type as 申请类型 from approval
inner join users on approval.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
inner join users AS users2 on apply.userId = users2.id 之前没注意,你试试
apply.time as 时间, approval.time as 上一镒审批时间, users.name as 审批人,
apply.state as 状态, apply.type as 申请类型 from approval
inner join users on approval.userid = users.id
inner join apply on approval.applyid = apply.id
inner join config on config.id = apply.configid
inner join users e on e.id = apply.userid
apply.time as 申请时间,approval.time as 上一次审批时间,
STUFF((SELECT ','+name FROM users WHERE approval.userId=id FOR XML PATH('')),1,1,'')as 审批人,
apply.state as 状态,apply.type as 申请类型
from approval
inner join users on apply.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
GROUP BY apply.id,config.name,users.name,apply.time,approval.time,users.name,apply.state,apply.type
apply.time as 申请时间,approval.time as 上一次审批时间,
STUFF((SELECT ','+name FROM users WHERE approval.userId=id FOR XML PATH('')),1,1,'')as 审批人,
apply.state as 状态,apply.type as 申请类型
from approval
inner join users on apply.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
GROUP BY apply.id,config.name,users.name,apply.time,approval.time,apply.state,apply.type
我像你这样打了,可是会提示我approval.userId没有包含在group by中,我加上了,查询结果就变成了这样了要不我把我的数据库发给你吧,帮我弄一下可以不,谢谢了
select apply.id as 申请编号,config.name as 配置节名称,users.name as 申请人,
apply.time as 申请时间,approval.time as 上一次审批时间,
users2.name AS 审批人,
apply.state as 状态,apply.type as 申请类型
from approval
inner join users on apply.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
inner join users AS users2 on approval.userId = users2.id
--如果有条件放在这里面。
)
SELECT 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型
,STUFF((SELECT ','+审批人 FROM CTE WHERE 申请编号=T1.申请编号 FOR XML PATH('')),1,1,'')as 审批人
FROM CTE T1
GROUP BY 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型
select apply.id as 申请编号,config.name as 配置节名称,users.name as 申请人,
apply.time as 申请时间,approval.time as 上一次审批时间,
users2.name AS 审批人,
apply.state as 状态,apply.type as 申请类型
from approval
inner join users on apply.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
inner join users AS users2 on approval.userId = users2.id
--如果有条件放在这里面。
)
SELECT 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型
,STUFF((SELECT ','+审批人 FROM CTE WHERE 申请编号=T1.申请编号 FOR XML PATH('')),1,1,'')as 审批人
FROM CTE T1
GROUP BY 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型我查询出来了,就是申请编号会有两条,怎么样把这两条数据整合成一个那,上一次审批时间是不一样的,以距离现在最近的时间为准,非常感谢
select apply.id as 申请编号,config.name as 配置节名称,users.name as 申请人,
apply.time as 申请时间,approval.time as 上一次审批时间,
users2.name AS 审批人,
apply.state as 状态,apply.type as 申请类型,
ROW_NUMBER()OVER(PARTITION BY apply.id ORDER BY approval.time DESC)RN
from approval
inner join users on apply.userId = users.id
inner join apply on approval.applyId = apply.id
inner join config on config.id = apply.congfigId
inner join users AS users2 on approval.userId = users2.id
--如果有条件放在这里面。
)
SELECT 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型
,STUFF((SELECT ','+审批人 FROM CTE WHERE 申请编号=T1.申请编号 FOR XML PATH('')),1,1,'')as 审批人
FROM CTE T1
WHERE RN=1
--GROUP BY 申请编号,配置节名称,申请人,申请时间,上一次审批时间,状态,申请类型试试