多语句表值函数与内联表值函数(Multi-statement Table Valued Function vs Inline Table Valued Function)
几个例子,显示,只是incase:
内联表价值
CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO
多重语句表值
CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO
使用一种类型(在线或多重语句)比其他语言有优势吗? 有一些情况是一个比另一个更好还是纯粹的语法差异? 我意识到两个示例查询正在做不同的事情,但是我有理由以这种方式写出来吗?
阅读他们和优点/差异还没有真正解释。
A few examples to show, just incase:
Inline Table Valued
CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO
Multi Statement Table Valued
CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO
Is there an advantage to using one type (in-line or multi statement) over the other? Is there certain scenarios when one is better than the other or are the differences purely syntactical? I realise the two example queries are doing different things but is there a reason I would write them in that way?
Reading about them and the advantages/differences haven't really been explained.
原文:https://stackoverflow.com/questions/2554333
最满意答案
我有一个这样的查询与我曾经写过的日历应用程序。 我想我用这样的东西:
... WHERE new_start < existing_end AND new_end > existing_start;
更新这应该是有效的((ns,ne,es,ee)=(new_start,new_end,existing_start,existing_end)):
- ns-ne-esee:不重叠并且不匹配(因为ne <es)
- ns - es - nee ee:重叠和匹配
- es - ns - ee - ne:重叠和匹配
- es - ee - ns - ne:不重叠,不匹配(因为ns> ee)
- es - ns - nee ee:重叠和匹配
- ns - es - ee - ne:重叠和匹配
这是一个小提琴
I had such a query with a calendar application I once wrote. I think I used something like this:
... WHERE new_start < existing_end AND new_end > existing_start;
UPDATE This should definitely work ((ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end)):
- ns - ne - es - ee: doesn't overlap and doesn't match (because ne < es)
- ns - es - ne - ee: overlaps and matches
- es - ns - ee - ne: overlaps and matches
- es - ee - ns - ne: doesn't overlap and doesn't match (because ns > ee)
- es - ns - ne - ee: overlaps and matches
- ns - es - ee - ne: overlaps and matches
Here is a fiddle
相关问答
更多-
有什么数据库专业书籍介绍?[2022-05-19]
mysql -
很容易确定给定的时期是否与另一个时期不重叠。 为了便于表达比较,对于第1期,我们将开头和结尾用b1和e1表示。 对于时段2,b2和e2。 如果满足以下条件,则不会重叠: b1 > e2 OR e1 < b2 (我们可以狡辩b1和e2的相等是否被视为重叠,并根据需要进行调整。) 如果存在重叠,则对该测试的否定将返回TRUE ... NOT (b1 > e2 OR e1 < b2) 因此,要查明是否存在与建议期间重叠的行,我们需要一个测试是否返回行的查询... 让我们假设我们要检查的表有列st和et ...
-
此函数将处理4个时间戳的任何输入。 CREATE OR REPLACE FUNCTION date_overlap(start1 timestamp, end1 timestamp, start2 timestamp, end2 timestamp) RETURNS int STRICT AS ' -- STRICT returns NULL if any input is NULL BEGIN -- for valid ranges, end1 must be after (or e ...
-
我有一个这样的查询与我曾经写过的日历应用程序。 我想我用这样的东西: ... WHERE new_start < existing_end AND new_end > existing_start; 更新这应该是有效的((ns,ne,es,ee)=(new_start,new_end,existing_start,existing_end)): ns-ne-esee:不重叠并且不匹配(因为ne(StartA <= EndB)和(EndA> = StartB) 证明: 条件A意味着DateRange A完全在DateRange之后B _ |---- DateRange A ------| |---Date Range B -----| _ (如果StartA > EndB True) 让条件B意味着DateRange A完全在DateRange B之前 |---- DateRange A -----| _ _ |---Date Range B ----| (如果EndA < StartB True ...在这种情况下拥抱Grails / GORM的两种方式: 懒:- def today = new Date() def query = MyObject.where { startDate <= (today - 10) && (endDate == null || endDate >= today + 10) } def listOfMyObjects = query.list() 急于:- def today = new Date() def listOfMyObjects = MyObject ...你可以使用Joda-Time 。 它提供了Interval类,它指定了开始和结束时刻,并可以检查重叠的overlaps(Interval) 。 就像是 DateTime now = DateTime.now(); DateTime start1 = now; DateTime end1 = now.plusMinutes(1); DateTime start2 = now.plusSeconds(50); DateTime end2 = now.plusMinutes(2); Interval int ...
重叠的日期范围MySQL(Overlapping date range MySQL)[2022-02-14]
一个查询选择重叠(我会命名列startTime&endTime,但时间似乎很重要......): WHERE< endDate AND > startDate A query to select overlaps (I'd name the columns startTime & endTime though, as Time seems important...): WHERE < endDate AND > startDate 这是答案: select t.customerid, t.eff_dt, count(distinct t2.productId), MIN(t2.end_dt) as end_dt from #tmp t join #tmp t2 on t.CustomerID = t2.CustomerID and t.Eff_Dt between t2.Eff_Dt and t2.End_Dt group by t.CustomerID, t.eff_dt havin ...相关文章
更多- function-Oceanus配置说明
- table-Oceanus配置说明
- Lua 元表(Metatable)改变table的行为
- Lua table(表、数组、字典)
- 两种js function 声明方式
- error C2668: 'M' : ambiguous call to overloaded function
- Guava学习笔记:Guava新集合-Table等
- 求教一个 table的css
- Guava集合工具类-Table接口映射处理
- div中table100%宽度的浏览器兼容性问题
最新问答
更多- 您如何使用git diff文件,并将其应用于同一存储库的副本的本地分支?(How do you take a git diff file, and apply it to a local branch that is a copy of the same repository?)
- 将长浮点值剪切为2个小数点并复制到字符数组(Cut Long Float Value to 2 decimal points and copy to Character Array)
- OctoberCMS侧边栏不呈现(OctoberCMS Sidebar not rendering)
- 页面加载后对象是否有资格进行垃圾回收?(Are objects eligible for garbage collection after the page loads?)
- codeigniter中的语言不能按预期工作(language in codeigniter doesn' t work as expected)
- 在计算机拍照在哪里进入
- 使用cin.get()从c ++中的输入流中丢弃不需要的字符(Using cin.get() to discard unwanted characters from the input stream in c++)
- No for循环将在for循环中运行。(No for loop will run inside for loop. Testing for primes)
- 单页应用程序:页面重新加载(Single Page Application: page reload)
- 在循环中选择具有相似模式的列名称(Selecting Column Name With Similar Pattern in a Loop)
- System.StackOverflow错误(System.StackOverflow error)
- KnockoutJS未在嵌套模板上应用beforeRemove和afterAdd(KnockoutJS not applying beforeRemove and afterAdd on nested templates)
- 散列包括方法和/或嵌套属性(Hash include methods and/or nested attributes)
- android - 如何避免使用Samsung RFS文件系统延迟/冻结?(android - how to avoid lag/freezes with Samsung RFS filesystem?)
- TensorFlow:基于索引列表创建新张量(TensorFlow: Create a new tensor based on list of indices)
- 企业安全培训的各项内容
- 错误:RPC失败;(error: RPC failed; curl transfer closed with outstanding read data remaining)
- C#类名中允许哪些字符?(What characters are allowed in C# class name?)
- NumPy:将int64值存储在np.array中并使用dtype float64并将其转换回整数是否安全?(NumPy: Is it safe to store an int64 value in an np.array with dtype float64 and later convert it back to integer?)
- 注销后如何隐藏导航portlet?(How to hide navigation portlet after logout?)
- 将多个行和可变行移动到列(moving multiple and variable rows to columns)
- 提交表单时忽略基础href,而不使用Javascript(ignore base href when submitting form, without using Javascript)
- 对setOnInfoWindowClickListener的意图(Intent on setOnInfoWindowClickListener)
- Angular $资源不会改变方法(Angular $resource doesn't change method)
- 在Angular 5中不是一个函数(is not a function in Angular 5)
- 如何配置Composite C1以将.m和桌面作为同一站点提供服务(How to configure Composite C1 to serve .m and desktop as the same site)
- 不适用:悬停在悬停时:在元素之前[复制](Don't apply :hover when hovering on :before element [duplicate])
- 常见的python rpc和cli接口(Common python rpc and cli interface)
- Mysql DB单个字段匹配多个其他字段(Mysql DB single field matching to multiple other fields)
- 产品页面上的Magento Up出售对齐问题(Magento Up sell alignment issue on the products page)