周一至周五 : 08:30 - 17:30 客服专员电话/微信:17301649370 QQ:2902102332
微信咨询

扫码微信咨询

关注公众号

关注微信公众号

电话: 021 5161 9370
获取报价
个人中心
我的收藏
私信列表
消息中心
搜索全站

扫码关注微信公众号

微信咨询客服
返回顶部
提取最近的数据
hessen
显示全部楼层 倒序浏览 发表日期 2020-07-08 17:34:27 2344次阅读 4次回复
阅读排行
返回列表 发新帖
快表软件 › 阶段二：设计系统 › 表间公式 提取最近的数据
楼主
hessen
2020-7-8 17:34:27
显示全部楼层
本帖最后由 hessen 于 2020-7-8 17:36 编辑

取各个单位最后一次填写的那笔记录，这里是SQL示例，可以直接写在快表中。如果不会联系我。
  1. CREATE TABLE [dbo].[T_565](
  2.         [ID] [varchar](50) NULL,
  3.         [CUID] [varchar](50) NULL,
  4.         [CDID] [varchar](50) NULL,
  5.         [CUNAME] [nvarchar](255) NULL,
  6.         [CDATE] [datetime] NULL,
  7.         [EUID] [varchar](50) NULL,
  8.         [EUNAME] [nvarchar](255) NULL,
  9.         [EDATE] [datetime] NULL,
  10.         [WFID] [varchar](50) NULL,
  11.         [REPORTLOCK] [bit] NULL,
  12.         [REPORTEDIT] [bit] NULL,
  13.         [F_15222] [varchar](255) NULL,
  14.         [F_15223] [datetime] NULL
  15. ) ON [PRIMARY]
  16. GO
  17. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'1b104f8e-2cc6-47e7-845f-3222fadf477b', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:49:53.977' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'1', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  18. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'b2a6a811-c5f9-4f48-8fa5-76b1d8335a5c', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:49:56.043' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'2', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  19. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'437a7295-7ada-4320-9646-e9b307c380f4', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:49:57.890' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'3', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  20. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'15bd21e3-56aa-489a-bf3d-de54d53136d6', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:49:59.920' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'4', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  21. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'e1ce96e0-f731-455b-8a1a-db2ab45a3265', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:50:01.997' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'5', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  22. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'ad4147d8-c5e3-4ea8-ad1f-d9410ea47fa7', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:50:03.780' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'6', CAST(N'2020-07-08T00:00:00.000' AS DateTime))
  23. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'ea9b76eb-b47f-4c2c-8bce-08b671cc6392', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:02.547' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'1', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  24. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'c70e67b6-3777-411c-a795-da21a3f50087', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:04.167' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'2', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  25. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'806b625f-bbec-4415-84da-e03838ba89ae', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:05.560' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'3', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  26. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'6ba3047d-dab2-4954-a501-e01e7bc64c6c', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:06.727' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'4', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  27. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'bef3b2a1-fbda-4be5-9184-fbe0e4e095d3', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:07.860' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'5', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  28. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'50d03b46-2aba-49fe-b0e8-f958aac828c6', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:09.020' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'6', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  29. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'f96bdaed-b48a-49ae-aff3-b249852024b0', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:10.147' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'7', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  30. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'7ab50ed3-6b06-440b-b3ca-44aefa3d36ec', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:11.243' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'8', CAST(N'2020-07-07T00:00:00.000' AS DateTime))
  31. INSERT [dbo].[T_565] ([ID], [CUID], [CDID], [CUNAME], [CDATE], [EUID], [EUNAME], [EDATE], [WFID], [REPORTLOCK], [REPORTEDIT], [F_15222], [F_15223]) VALUES (N'06e12bd9-be45-4001-8816-13c8f15e6f91', N'0e6c2110-37a6-40e0-8fd9-a82edad11bae', N'91c8f637-37a4-4117-992a-d5c425418cd8', N'超级管理员', CAST(N'2020-07-08T15:51:12.330' AS DateTime), NULL, NULL, NULL, N'', NULL, NULL, N'9', CAST(N'2020-07-07T00:00:00.000' AS DateTime))

  33. --数据准备完毕下面查询

  35. WITH C AS(
  36. SELECT ROW_NUMBER() OVER(PARTITION BY F_15222 ORDER BY F_15223 DESC) AS NO,F_15222 AS A,F_15223 AS B FROM T_565
  37. )
  38. SELECT A,B FROM C WHERE NO = 1 ORDER BY B

  40. --得到最近的数据
复制代码


效果如下图:
20200708173551.png



电话/微信：18049989370 QQ：857188287
回复

举报

发布主题
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则  允许回帖邮件提醒楼主

快表软件是国内较早研究表格类软件开发平台的团队之一，迄今已有十多年的行业经验.致力于为企事业单位提供实用可靠的数字化平台。

  • 微信公众号

  • 微信小商店

  • 微信客服

  • Powered by Discuz! X3.4 | Copyright © 2022-2024, XiRong Soft. | 快表软件
  • 沪ICP备13033196号 | 营业执照 |上海西戎软件科技有限公司|沪公网安备31011502002146号|沪ICP备13033196号 |