SELECT ticket_number, some_id
FROM (
SELECT ticket_number, some_id,
rank() OVER (PARTITION BY ticket_number ORDER BY some_id DESC) RnkDESK,
rank() OVER (PARTITION BY ticket_number ORDER BY some_id ASC) RnkASC
FROM table ) x
WHERE X.RnkDESK = 1 OR X.RnkASC = 1CREATE FUNCTION SUMOFNUMBERS
(
@FirstNumber INT,
@SecondNumber INT
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Answer int
-- Add the T-SQL statements to compute the return value here
SET @Answer = @FirstNumber + @SecondNumber;
-- Return the result of the function
RETURN @Answer
ENDselect dbo.SUMOFNUMBERS(5,6)CREATE PROCEDURE SUMOFNUMBERS
@FirstNumber INT,
@SecondNumber INT,
AS
BEGIN
SET @Answer = @FirstNumber + @SecondNumber;
RETURN @Answer;
END
EXEC @Answer = SUMOFNUMBERS 5, 6ALTER TABLE [dbo].[OrderGoods] WITH CHECK ADD CONSTRAINT [FK_OrderGoods_OrderHeader] FOREIGN KEY([OrderHeaderGUID])
REFERENCES [dbo].[OrderHeader] ([GUID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderGoods] CHECK CONSTRAINT [FK_OrderGoods_OrderHeader]
GO
EXISTS (select 1 FROM oc_product_attribute_id p2a0 WHERE p2a0.product_id=p2a.product_id AND p2a0.text_id = 67)
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id)
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice
WHERE 1Я не знаток MySQL но я не понимаю зачем вам это???
WHERE p2a.attribute_id IS NOT NULL
public ObservableCollection<Person> FilteredCollection
{
get
{
return new ObservableCollection<Person>(PersonCollection.Where(i => FilteredCheck(i)));
}
}public bool FilteredCheck(DemandHead item)
{
bool Accept=true;
// организовываем проверку как вашей душе удобно
}
// И дальше при изменении фильтра оповещаем View об изменении данных
public bool IsFilterEnabled
{
get
{
return _IsFilterEnabled;
}
set
{
_IsFilterEnabled = value;
base.OnPropertyChanged("IsFilterEnabled");
base.OnPropertyChanged("FilteredCollection");
}
}CREATE FUNCTION [dbo].[GetDateRangeShort] (@StartDate datetime, @EndDate datetime)
RETURNS @Res TABLE (Date datetime) AS
BEGIN
;WITH MyEmp (Date, Level)
AS(
SELECT @StartDate, 1
UNION ALL
SELECT DATEADD(dd, 1, Date), Level + 1
FROM MyEmp
WHERE (Date < @EndDate)
)
INSERT INTO @Res (Date) SELECT Date FROM MyEmp OPTION (MAXRECURSION 0);
RETURN
ENDSELECT
FROM TABLE
inner join TABLE2 ON ...
inner join TABLE3 ON ...SELECT * FROM
[OTD_db].[dbo].[DocumentsA] As [CurStatID]
INNER JOIN [OTD_db].[dbo].[Status] AS [Vv1] ON [Vv1].[StatusID] = [CurStatID].[CurrentStatusID] and [Vv1].[Name] Like 'Шаблон'ALTER PROCEDURE [dbo].[LockUnLockOrder]
@HeadGUID uniqueidentifier, @UserGUID uniqueidentifier, @Lock bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @HeadGUID is null or @UserGUID is null or @Lock is null
begin
raiserror('Процедура LockUnLockOrder: Неинициализированный параметр',16,1)
return -1
end
if V_ORDER.dbo.CheckEditStatus(@HeadGUID,@UserGUID) = 1
begin
if @Lock = 1
UPDATE [V_ORDER].[dbo].[OrderHeader]
SET isEdit = 1
,BeginEdit = getdate()
,UserEdit = @UserGUID
WHERE GUID = @HeadGUID
else
if @Lock = 0
UPDATE [V_ORDER].[dbo].[OrderHeader]
SET isEdit = 0
,BeginEdit = null
,UserEdit = null
WHERE GUID = @HeadGUID
return 1
end
else raiserror('Невозможно изменить статус заказа',16,1)
end-- =============================================
-- Author: k1lex
-- Create date: 20160818
-- Description: функция возвращает разрешение редактирования заказа. 1 - можно редактировать. 0 - нельзя
-- =============================================
ALTER FUNCTION [dbo].[CheckEditStatus]
(
-- Add the parameters for the function here
@HeadGUID uniqueidentifier, @UserGUID uniqueidentifier
)
RETURNS bit
AS
BEGIN
DECLARE @bit BIT
IF (
SELECT COUNT(*)
FROM [V_ORDER].[dbo].[OrderHeader] O WITH (NOLOCK)
WHERE O.GUID = @HeadGUID
AND (ISNULL(O.IsReadOnly, 0) = 1 OR (O.userEdit != @UserGUID AND O.IsEdit = 1 AND DATEDIFF(HOUR, BeginEdit, GETDATE()) < 1)
and [Status]=1
)
) = 0
SET @bit = 1
ELSE
SET @bit = 0
RETURN @bit
ENDselect
t1.id , t1.project_id , t1.sum_1 , t1.sum_2
from table t1
inner join
( select project_id , COUNT(sum_1) ac CT from table group by project_id ) t2 ON t1.project_id=t2.project_id and t2.CT>1
inner join
( select project_id , COUNT(sum_2) ac CT from table group by project_id ) t3 ON t1.project_id=t3.project_id and t3.CT=1SELECT [Поле группировки], sum(что сложить) FROM `table`
WHERE <условие>
group by [Поле группировки]SELECT [Поле группировки], sum(что сложить) FROM `table`
group by [Поле группировки]
having [условие]IF OBJECT_ID('tempdb..#testtable') IS NOT NULL DROP TABLE #testtable
create table #testtable (number nvarchar(255), spac money, dateR date)
insert into #testtable
(number, spac, dateR)
values
(1,10,'20140505'),
(1,11,'20141212'),
(2,50,'20160505'),
(2,40,'20160508')
IF OBJECT_ID('tempdb..#testtableRNK') IS NOT NULL DROP TABLE #testtableRNK
select * ,
rank() over (partition by Number order by dateR desc) as RNK
into #testtableRNK
from #testtable
select * from #testtableRNK r1
inner join #testtableRNK r2 ON r1.number= r2.number and r2.RNK=(r1.RNK+1) and r1.RNK=1 and r2.spac< r1.spac